﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Project_ScansiaPacific
{
    public partial class FrKTG : DevComponents.DotNetBar.Office2007Form
    {
        public FrKTG()
        {
            InitializeComponent();
        }
        private void Enable()
        {
            tab_Nhap.Visible = true;
            tab_Xuat.Visible = true;
        }
        private void Disable()
        {
            tab_Nhap.Visible = false;
            tab_Xuat.Visible = false;
        }
        private void Check_User()
        {
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                Conn.Open();
                string check = @"select GroupUser from dbo.Users where UsersName='" + lb_user.Text + "'";
                cmd.Connection = Conn;
                cmd.CommandText = check;
                //MessageBox.Show(check);
                check = cmd.ExecuteScalar().ToString();
                if (check == "thongke" || check == "admin" || check == "nhaplieu")
                    Enable();
                else
                    Disable();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private string MaSP1, TenSP1, MaCT1, TenCT1, SoBB1, SoKH1, SLNhap1, SLXuat1, Ngay_start1, Ngay_End1,child=" - ",Ngay1,NgayInsert1,UserNhap,UserXuat,MaCTC,GhiChu1,flag1,SLChuyen1,SoXe1,string_temp,FSC1;
        private double Tong1 = 0, SLNhap_d = 0, SLXuat_d = 0, TK_KTG,KH_DP,SLKH_N;
        private int check, i, j, k;
        private void combo_load()
        {
           
            Data dta = new Data();
            FormProcess Frp = new FormProcess();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            Conn.Open();
            cmd.Connection = Conn;

            try
            {
                cmd.CommandText = "select convert(varchar(10),getdate(),101)";

                string sql_load = @"select * from dbo.Load_cbSP";
                Frp.cb1(cb_MaSPinput, sql_load, 1, 0);
                Frp.cb1(cb_MaSPoutput, sql_load, 1, 0);
                Frp.cb1(cb_MaSPTK, sql_load, 1, 0);
                Frp.cb1(cb_MaSPReport, sql_load, 1, 0);
                Frp.cb1(cb_MaSP_C, sql_load, 1, 0);
                Frp.cb1(cb_MaSP_N, sql_load, 1, 0);
                DataSet ds = new DataSet();
                string_temp = @"select dbo.Load_CTSP_Mua.MaSP,dbo.Load_CTSP_Mua.TenChiTiet+' - '+dbo.Load_CTSP_Mua.MaChiTiet from dbo.Load_CTSP_Mua inner join dbo.ChiTiet_KhuVuc on ChiTiet_KhuVuc.MaCT=dbo.Load_CTSP_Mua.MaChiTiet and ChiTiet_KhuVuc.KhuVuc='KV1N'";
                ds = dta.dts(string_temp);
                for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    dataGridView_LoadSP.Rows.Add(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString());
                }
                ds.Clear();
                string_temp = cmd.ExecuteScalar().ToString();
                txt_Ngay_C.Text = string_temp;
                txt_NgayBK.Text = string_temp;
                txt_Ngayend.Text = string_temp;
                txt_Ngayinput.Text = string_temp;
                txt_Ngayoutput.Text = string_temp;
                txt_NgayStart.Text = string_temp;

                dataGridView1.Columns["SLNhap"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView3.Columns["SoLuongXuat"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView5.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[9].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
                dataGridView6.Columns[10].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void cb_MaSP_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            //FormProcess Frp = new FormProcess();
            MaSP1 = prs.substring_right(cb_MaSPinput.Text, child);
            cb_MaCTinput.Items.Clear();
            cb_MaCTinput.Text = "";
            //string sql_load = @"select MaChiTiet,TenChiTiet from dbo.Load_CTSP_Mua inner join dbo.ChiTiet_KhuVuc on ChiTiet_KhuVuc.MaCT=dbo.Load_CTSP_Mua.MaChiTiet and ChiTiet_KhuVuc.KhuVuc='KV1N' where dbo.Load_CTSP_Mua.MaSP='" + MaSP1 + "'";
           // MessageBox.Show(dataGridView_LoadSP.Rows[1].Cells[0].Value.ToString());
            //Frp.cb1(cb_MaCTinput, sql_load, 1, 0);
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCTinput.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
        }

        private void txt_SLNhap_TextChanged(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SLinput);
        }

        private void FrKTG_Load(object sender, EventArgs e)
        {
            try
            {
                combo_load();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //bt_insert.BackColor = Color.Red;
            //
        }
        private void add_grid()
        {
            try
            {
                Process prs = new Process();
                TenSP1 = prs.substring_left(cb_MaSPinput.Text, child);
                TenCT1 = prs.substring_left(cb_MaCTinput.Text, child);
                MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                SoKH1 = prs.substring_right(txt_SoKHinput.Text, child);
                if (TenSP1 == "" || MaCT1 == "" || txt_Ngayinput.Text == "" || txt_SLinput.Text == "" || SoKH1 == "")
                {
                    MessageBox.Show("Thông Tin Nhập Không Chính Xác Hoặc Thiếu");
                }
                else
                {
                    i = 0;
                    if (dataGridView1.Rows.Count > 1)
                    {
                        while (i < dataGridView1.Rows.Count - 1)
                        {
                            if (MaCT1 != dataGridView1.Rows[i].Cells["MaCT"].Value.ToString())
                                i++;
                            else
                                break;
                        }
                    }
                    if (dataGridView1.Rows.Count == 1 || i == dataGridView1.Rows.Count - 1)
                    {
                        k = cb_MaCTinput.Items.Count - 1;
                        while (k >= 0)
                        {
                            
                            if (MaCT1 == prs.substring_right(cb_MaCTinput.Items[k].ToString(), child))
                                break;
                            else
                                k--;
                        }
                        j = txt_SoKHinput.Items.Count - 1;
                        //MessageBox.Show(j.ToString());
                        while (j >= 0)
                        {
                            if (SoKH1 == prs.substring_right(txt_SoKHinput.Items[j].ToString(), child))
                                break;
                            else
                                j--;
                        }
                        if (k >= 0&&j>=0)
                        {
                            SoBB1 = txt_SoBBinput.Text.Trim();
                            //SL1 = txt_SLInput.Text.Trim();
                            FSC1 = txt_FSCinput.Text.Trim();
                            Ngay1 = txt_Ngayinput.Text.Trim();
                            SLNhap1 = txt_SLinput.Text;
                            string[] temp = txt_Ngayinput.Text.Split('/');
                            NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                            GhiChu1 = txt_GhiChuinput.Text;
                            dataGridView1.Rows.Add(TenSP1, MaCT1, TenCT1, FSC1, SoKH1, SLNhap1, Ngay1, SoBB1,NgayInsert1, GhiChu1,"0");
                            j = dataGridView1.Rows.Count - 1;
                            dataGridView1.CurrentCell = dataGridView1.Rows[j].Cells[0];
                            dataGridView1.FirstDisplayedCell = dataGridView1.Rows[j].Cells[0];
                            prs.formatcolordatagrid(dataGridView1);
                            //prs.reset_txt(groupPanel1);
                            txt_SLinput.Text = "";
                            cb_MaCTinput.Text = "";
                            txt_GhiChuinput.Text = "";
                            Tong1 = double.Parse(lb_Tong.Text);
                            Tong1 += double.Parse(SLNhap1);
                            lb_Tong.Text = Tong1.ToString();
                            lb_Tong.Text = string.Format("{0:###,###}", Tong1);
                            lb_SLKHN.Text = "0";
                            lb_KHDP.Text = "0";
                            cb_MaSPinput.Focus();
                            txt_SoKHinput.Text = "";
                        }
                        else
                            MessageBox.Show("Kiểm tra lại Mã CT hoặc Mã KH nhập");
                    }
                    else
                        MessageBox.Show("Chi Tiết này đã có trong DS phía dưới"); 
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_insert_Click(object sender, EventArgs e)
        {
            add_grid();
        }
        private void save_input()
        {
            // int i;
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Tran_Ins_Up";
            cmd.CommandType = CommandType.StoredProcedure;
            Conn.Open();
            cmd.Connection = Conn;
            
            try
            {
                UserXuat="";
                if (dataGridView1.Rows.Count > 1)
                {
                    
                    for (i = dataGridView1.Rows.Count-2; i >= 0; i--)
                    {
                        //MaCT1 = "'Null'";
                        
                        MaCT1 = dataGridView1.Rows[i].Cells["MaCT"].Value.ToString();
                        SoKH1 = dataGridView1.Rows[i].Cells["SoKH"].Value.ToString();
                        FSC1 = dataGridView1.Rows[i].Cells["FSCinput"].Value.ToString();
                        SoBB1 = dataGridView1.Rows[i].Cells["SoBB"].Value.ToString();
                        SLNhap_d = double.Parse(dataGridView1.Rows[i].Cells["SLNhap"].Value.ToString());
                        GhiChu1 = dataGridView1.Rows[i].Cells["GhiChuinput"].Value.ToString().Trim();
                        flag1=dataGridView1.Rows[i].Cells["flag"].Value.ToString();
                        Ngay1 = dataGridView1.Rows[i].Cells["NgayInsert"].Value.ToString();
                        UserNhap = lb_user.Text;
                        //insert_NhapKTG = @"insert into dbo.KV1_KTG(MaCT,SoBB,SoKH,SLNhap,Ngay,UserNhap,GhiChu)values(N'" + MaCT1 + "',N'" + SoBB1 + "',N'" + SoKH1 + "'," + SLNhap_d + ",'" + Ngay1 + "',N'" + UserNhap + "',N'"+GhiChu1+"')";
                        cmd.Parameters.AddWithValue("@MaCT", MaCT1);
                        cmd.Parameters.AddWithValue("@SoBB", SoBB1);
                        cmd.Parameters.AddWithValue("@SoKH", SoKH1);
                        cmd.Parameters.AddWithValue("@SLNhap", SLNhap_d);
                        cmd.Parameters.AddWithValue("@SLXuat", "0");
                        cmd.Parameters.AddWithValue("@Ngay", Ngay1);
                        cmd.Parameters.AddWithValue("@FSC", FSC1);
                        cmd.Parameters.AddWithValue("@UserNhap", UserNhap);
                        cmd.Parameters.AddWithValue("@UserXuat", UserXuat);
                        cmd.Parameters.AddWithValue("@GhiChu", GhiChu1);
                        cmd.Parameters.AddWithValue("@flag",flag1 );
                        cmd.Parameters.AddWithValue("@ID", SoKH1);
                        cmd.Parameters.AddWithValue("@SLTheoDoi", SLNhap_d);
                        cmd.Parameters.AddWithValue("@SoXe", "");
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        dataGridView1.Rows.RemoveAt(i);
                        
                    }
                    MessageBox.Show("Dữ Liệu được lưu thành công");
                    lb_Tong.Text = "0";
                    dta.reset_txt(groupPanel1);
                }
                


            }
            catch (Exception ex)
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
                MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_save_Click(object sender, EventArgs e)
        {
            save_input();
        }
        private void edit_input()
        {
            try
            {
                MessageBox.Show("Phần mềm không hỗ trợ chức năng này, bạn có thể xóa dữ liệu sai, sau đó thêm mới");
                /*string datecheck;
                int date_count;
                Process prs = new Process();
                SqlConnection Conn = prs.Connect();
                SqlCommand cmd = new SqlCommand();
                DialogResult d = MessageBox.Show("Bạn có chắc muốn sửa Thông Tin có Số Serial " + lb_serialinput.Text, "Thông Báo", MessageBoxButtons.OKCancel);
                MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                Ngay1 = txt_Ngayinput.Text;
                SoBB1 = txt_SoBBinput.Text;
                SoKH1 = txt_SoKHinput.Text;
                GhiChu1 = txt_GhiChuinput.Text;
                SLNhap_d = double.Parse(txt_SLinput.Text);
                string[] temp = Ngay1.Split('/');
                Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                Conn.Open();
                cmd.Connection = Conn;
                datecheck = @"select datediff(dd,NgayInsert,getdate()) from dbo.KV1_KTG where Serial='" + lb_serialinput.Text + "'";
                if (d == DialogResult.OK)
                {
                    cmd.CommandText = datecheck;
                    date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1)
                    {

                        string update_XP = @"UPDATE [DataBase_ScansiaPacific].[dbo].[KV1_KTG] SET MaCT='"+MaCT1+"',SoBB='"+SoBB1+"',SoKH='"+SoKH1+"',SLNhap="+SLNhap_d+",Ngay='"+Ngay1+"',Useredit='"+lb_user.Text+"',GhiChu=N'"+GhiChu1+"' where Serial='"+lb_serialinput.Text+"'";
                            // MessageBox.Show(update_XP);
                            cmd.CommandText = update_XP;
                            cmd.ExecuteNonQuery();
                            MessageBox.Show("Chỉnh Sửa Thành Công");
                    }
                    else
                        MessageBox.Show("Không được phép chỉnh sửa");
                    Conn.Close();
                    cmd.Dispose();
                    for (int i = 0; i < dataGridView2.Rows.Count - 1; i++)
                    {
                        dataGridView2.Rows.RemoveAt(0);
                    }
                    prs.reset_txt(groupPanel1);
                    bt_edit.Enabled = false;
                    bt_delete.Enabled = false;
                    lb_serialinput.Text = "0";
                }*/
            }
            catch (Exception ex)
            {
                string s = "The conversion of a char data type to a datetime";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Ngày Tháng Nhập chưa đúng"); }
                MessageBox.Show(ex.Message);
            }
             
        }
        private void Display_Input(int i)
        {
            try
            {
                MaSP1 = dataGridView2.Rows[i].Cells["MaSP"].Value.ToString();
                TenSP1 = dataGridView2.Rows[i].Cells["TenSP"].Value.ToString();
                MaCT1 = dataGridView2.Rows[i].Cells["MaCT"].Value.ToString();
                TenCT1 = dataGridView2.Rows[i].Cells["TenChiTiet"].Value.ToString();
                cb_MaSPinput.Text = TenSP1 + " - " + MaSP1;
                cb_MaCTinput.Text = TenCT1 + " - " + MaCT1;
                txt_SoBBinput.Text = dataGridView2.Rows[i].Cells["SoBB"].Value.ToString();
                txt_SoKHinput.Text = dataGridView2.Rows[i].Cells["SoKH"].Value.ToString();
                txt_SLinput.Text = dataGridView2.Rows[i].Cells["SLNhap"].Value.ToString();
               
                txt_Ngayinput.Text = dataGridView2.Rows[i].Cells["Ngay"].Value.ToString();
                
                txt_GhiChuinput.Text = dataGridView2.Rows[i].Cells["GhiChu"].Value.ToString();
                lb_SLNhap.Text = dataGridView2.Rows[i].Cells["SLNhap"].Value.ToString();
                lb_serialinput.Text = dataGridView2.Rows[i].Cells["Serial"].Value.ToString();
                lb_SoKHinput.Text = dataGridView2.Rows[i].Cells["SoKH"].Value.ToString();
                //MessageBox.Show(lb_SoKHinput.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void search_Input()
        {
            try
            {
                string search = null;
                Process prs = new Process();
                DataSet ds = new DataSet();
                if (txt_SoKHinput.Text != "")
                {
                    search = @"select qry1.Serial,ChiTietSP.TenSP,ChiTietSP.MaSP,ChiTietSP.TenChiTiet,qry1.MaCT,qry1.SoBB,qry1.SoKH,qry1.SLNhap,qry1.Ngay,qry1.GhiChu from
                           (select Serial,MaCT,SoBB,SoKH,SLNhap,Ngay,GhiChu from dbo.KV1_KTG where SoKH='" + txt_SoKHinput.Text + "' and SLNhap <>0) as qry1 inner join dbo.ChiTietSP on qry1.MaCT=ChiTietSP.MaChiTiet";
                }
                else
                {
                    if (cb_MaCTinput.Text != "" && txt_Ngayinput.Text != "")
                    {
                        MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                        string[] temp = txt_Ngayinput.Text.Split('/');
                        Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    }
                    if (MaCT1 == "")
                        MessageBox.Show("Mã CT không chính xác");
                    else
                    {
                        search = @"select qry1.Serial,ChiTietSP.TenSP,ChiTietSP.MaSP,ChiTietSP.TenChiTiet,qry1.MaCT,qry1.SoBB,qry1.SoKH,qry1.SLNhap,qry1.Ngay,qry1.GhiChu from (select Serial,MaCT,SoBB,SoKH,SLNhap, Ngay,GhiChu from dbo.KV1_KTG where MaCT='" + MaCT1 + "' and Ngay='" + Ngay1 + "' and SLNhap <>0) as qry1 inner join dbo.ChiTietSP on qry1.MaCT=ChiTietSP.MaChiTiet";
                    }
                }
                ds = prs.dts(search);
                if (ds.Tables[0].Rows.Count == 0)
                    MessageBox.Show("Không có dữ liệu, vui lòng kiểm tra lại thông tin tìm kiếm");
                else
                {
                    dataGridView2.DataSource = ds.Tables[0];
                    int k = dataGridView2.Rows.Count - 2;
                    Display_Input(k);
                    lb_index.Text = k.ToString();
                    bt_edit.Enabled = true;
                    bt_delete.Enabled = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_search_Click(object sender, EventArgs e)
        {
            search_Input();
            bt_insert.Enabled = false;
        }

        private void bt_previnput_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView2.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_index.Text);
                    if (i > 0)
                    {
                        i--;
                        Display_Input(i);
                        lb_index.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void bt_nextinput_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_index.Text);
                    if (i < dataGridView2.Rows.Count - 2)
                    {
                        i++;
                        Display_Input(i);
                        lb_index.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void bt_edit_Click(object sender, EventArgs e)
        {
            edit_input();
        }

        private void bt_delete_Click(object sender, EventArgs e)
        {
            DialogResult d = MessageBox.Show("Bạn có chắc muốn xóa Thông Tin có Số Serial " + lb_serialinput.Text, "Thông Báo", MessageBoxButtons.OKCancel);
            if (d == DialogResult.OK)
            {
                try
                {
                    string datecheck = @"select datediff(dd,NgayInsert,getdate()) from dbo.KV1_KTG where Serial='" + lb_serialinput.Text + "'";
                    
                    //string delete = @"delete from dbo.KV1_KTG where Serial='" + lb_serialinput.Text + "'";
                    Data dta = new Data();
                    SqlConnection Conn = dta.Connect();
                    Conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = Conn;
                    cmd.CommandText = datecheck;
                    
                    int date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1 || lb_user.Text == "administrator")
                    {
                        string Serial1 = lb_serialinput.Text;
                        SLNhap_d = double.Parse(lb_SLNhap.Text);
                        SoKH1 = lb_SoKHinput.Text;
                        cmd.CommandText = @"select flag from dbo.KV1_KTG where Serial="+Serial1;
                        flag1 = cmd.ExecuteScalar().ToString();
                        if (flag1 == "0")
                        {
                            cmd.CommandText = "Tran_Del_Up";
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@Serial", Serial1);
                            cmd.Parameters.AddWithValue("@ID", SoKH1);
                            cmd.Parameters.AddWithValue("@SLTheoDoi", SLNhap_d);
                            cmd.ExecuteNonQuery();
                            MessageBox.Show("Dữ liệu được xóa thành công");



                            lb_serialinput.Text = "0";
                            lb_SoKHinput.Text = "0";
                            cmd.Parameters.Clear();
                        }
                        else
                            MessageBox.Show("Serial này thuộc nhóm chuyển, liên hệ người quản trị phần mềm nếu muốn xóa");
                    }
                    else
                    {
                        MessageBox.Show("Quá thời gian cho phép,Không được phép xóa");
                    }
                    Conn.Close();
                    cmd.Dispose();
                    for (i = dataGridView2.Rows.Count-2; i >=0; i--)
                    {
                        dataGridView2.Rows.RemoveAt(i);
                    }
                    dta.reset_txt(groupPanel1);
                    txt_SoKHinput.Items.Clear();
                    txt_SoKHinput.Text = "";
                    cb_MaCTinput.Text = "";
                    bt_delete.Enabled = false;
                    bt_edit.Enabled = false;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

       
        private void cb_MaSPXuat_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
           
            MaSP1 = prs.substring_right(cb_MaSPoutput.Text, child);
            cb_MaCToutput.Items.Clear();
            cb_CTChuyen.Items.Clear();
            cb_MaCToutput.Text = "";
            for (i = dataGridView_LoadSP.Rows.Count - 2; i >= 0; i--)
            {
                if (MaSP1 == dataGridView_LoadSP.Rows[i].Cells[0].Value.ToString())
                {
                    cb_MaCToutput.Items.Add(dataGridView_LoadSP.Rows[i].Cells[1].Value.ToString());
                }
            }
            
            
        }

        private void txt_SLXuat_TextChanged(object sender, EventArgs e)
        {
            try
            {
                FormProcess Frp = new FormProcess();
                Frp.checktext_number(txt_SLoutput);
                
            }
            catch
            {
                
            }
        }
        private void add_grid_out()
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection Conn = new SqlConnection();
            Process prs = new Process();
            Conn = prs.Connect();
            Conn.Open();
            cmd.Connection = Conn;

            try
            {
                TenSP1 = prs.substring_left(cb_MaSPoutput.Text, child);
                TenCT1 = prs.substring_left(cb_MaCToutput.Text, child);
                MaCT1 = prs.substring_right(cb_MaCToutput.Text, child);
                if (TenSP1 == "" || MaCT1 == "" || txt_Ngayoutput.Text == "" || txt_SLoutput.Text == "" || txt_SoBBoutput.Text == "")
                {
                    MessageBox.Show("Thông Tin Nhập Không Chính Xác Hoặc Thiếu");
                }
                else
                {
                    SoKH1 = prs.substring_right(cb_SoKHoutput.Text, child);
                    SoBB1 = txt_SoBBoutput.Text.Trim();
                    //SL1 = txt_SLXuat.Text.Trim();
                    Ngay1 = txt_Ngayoutput.Text.Trim();
                    SLXuat1 = txt_SLoutput.Text.Trim();
                    SoXe1 = txt_SoXe.Text;
                    GhiChu1 = txt_GhiChuoutput.Text;
                    FSC1 = txt_FSCoutput.Text.Trim();
                    string[] temp = Ngay1.Split('/');
                    NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    i = 0;
                    #region //Kiểm tra grid
                    if (dataGridView3.Rows.Count > 1)
                    {
                        while (i < dataGridView3.Rows.Count - 1)
                        {
                            if (MaCT1 != dataGridView3.Rows[i].Cells["MaCTXuat"].Value.ToString())
                                i++;
                            else
                                break;
                        }
                    }
                    if (dataGridView3.Rows.Count == 1 || i == dataGridView3.Rows.Count - 1)
                    {
                        #region //kiểm tra dữ liệu nhập
                        k = cb_MaCToutput.Items.Count - 1;
                        while (k >= 0)
                        {

                            if (MaCT1 == prs.substring_right(cb_MaCToutput.Items[k].ToString(), child))
                                break;
                            else
                                k--;
                        }
                        if (rd_ChuyenNoiBo.Checked == false)
                        {
                            j = cb_SoKHoutput.Items.Count - 1;

                            while (j >= 0)
                            {
                                if (SoKH1 == prs.substring_right(cb_SoKHoutput.Items[j].ToString(), child))
                                    break;
                                else
                                    j--;
                            }
                        }
                        else
                            j = 0;

                        if (k >= 0 && j >= 0)
                        {
                            if (rd_GiaoSoChe.Checked == true)
                            {
                                dataGridView3.Rows.Add(TenSP1, MaCT1, TenCT1, SoBB1, SoKH1, SLXuat1, Ngay1,FSC1, NgayInsert1, SoXe1, GhiChu1, "0");
                                txt_SLoutput.Text = "";
                                cb_MaCToutput.Text = "";
                                txt_GhiChuoutput.Text = "";
                                txt_SoXe.Text = "";
                                Tong1 = double.Parse(lb_TongXuat.Text);
                                Tong1 += double.Parse(SLXuat1);
                                //lb_TongXuat.Text = Tong1.ToString();
                                lb_TongXuat.Text = string.Format("{0:###,###}", Tong1);
                                cb_MaSPoutput.Focus();
                                rd_GiaoSoChe.Checked = true;
                                cb_CTChuyen.Visible = false;
                                rd_ChuyenNoiBo.Enabled = false;
                                rd_GiaoSoChe.Enabled = false;
                                txt_SoBBoutput.Text = (double.Parse(txt_SoBBoutput.Text) + 1).ToString();
                                prs.formatcolordatagrid(dataGridView3);
                            }
                            if (rd_ChuyenNoiBo.Checked == true)
                            {
                                if (cb_CTChuyen.Text != "")
                                {
                                    MaCTC = prs.substring_right(cb_CTChuyen.Text, child);
                                    check = cb_CTChuyen.Items.Count - 1;
                                    //MessageBox.Show(j.ToString());
                                    while (check >= 0)
                                    {
                                        if (MaCTC == prs.substring_right(cb_CTChuyen.Items[check].ToString(), child))
                                            break;
                                        else
                                            check--;
                                    }
                                    if (check >= 0)
                                    {
                                        GhiChu1 = "Chuyển Nội Bộ";
                                        MaSP1 = prs.substring_right(cb_MaSPoutput.Text, child);
                                        SoKH1 = MaSP1 + "KV1N" + MaCT1;
                                        cmd.CommandText = @"select SLKH-SLTheoDoi from dbo.KH_TheoDoi where ID='" + SoKH1 + "'";
                                        double TD = double.Parse(cmd.ExecuteScalar().ToString());
                                        if (TD == 0)
                                            MessageBox.Show(" KẾT CHUYỂN KHÔNG THÀNH CÔNG.\n Do Số lượng dự phòng hiện tại của Chi Tiết " + TenCT1 + " không có");
                                        else
                                        {
                                            if (TD < double.Parse(txt_SLoutput.Text))
                                            {
                                                SLXuat1 = TD.ToString();
                                                MessageBox.Show("Bạn chỉ chuyển được tối đa " + SLXuat1 + " chi tiết");
                                            }
                                            dataGridView3.Rows.Add(TenSP1, MaCT1, TenCT1, SoBB1, SoKH1, SLXuat1, Ngay1, NgayInsert1, SoXe1, GhiChu1, "2");
                                            SLXuat1 = "-" + SLXuat1;
                                            TenCT1 = prs.substring_left(cb_CTChuyen.Text, child);
                                            SoKH1 = MaSP1 + "KV1N" + MaCTC;
                                            dataGridView3.Rows.Add(TenSP1, MaCTC, TenCT1, SoBB1, SoKH1, SLXuat1, Ngay1, NgayInsert1, SoXe1, GhiChu1, "2");
                                            txt_SLoutput.Text = "";
                                            cb_MaCToutput.Text = "";
                                            txt_GhiChuoutput.Text = "";
                                            cb_MaSPoutput.Focus();
                                            rd_GiaoSoChe.Checked = true;
                                            cb_CTChuyen.Visible = false;
                                            rd_ChuyenNoiBo.Enabled = false;
                                            rd_GiaoSoChe.Enabled = false;
                                            txt_SoBBoutput.Text = (double.Parse(txt_SoBBoutput.Text) + 1).ToString();
                                            prs.formatcolordatagrid(dataGridView3);
                                        }
                                    }
                                    else
                                        MessageBox.Show("Kiểm tra lại Mã CT cần chuyển");
                                }
                                else
                                    MessageBox.Show("Nhập thiếu chi tiết chuyển");


                            }
                        }
                        #endregion
                        else
                            MessageBox.Show("Kiểm tra lại Mã CT hoặc Số KH");
                    }
                    #endregion
                    else
                        MessageBox.Show("Chi tiết này đã có trong DS phía dưới. Bạn cần lưu DS này trước");
                    j = dataGridView3.Rows.Count - 1;
                    dataGridView3.CurrentCell = dataGridView3.Rows[j].Cells[0];
                    dataGridView3.FirstDisplayedCell = dataGridView3.Rows[j].Cells[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_insertoutput_Click(object sender, EventArgs e)
        {
            add_grid_out();
        }
        private void save_output()
        {
            int i;
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                Conn.Open();
                cmd.Connection = Conn;
                cmd.CommandText = "Tran_Ins_Up";
                    if (dataGridView3.Rows.Count > 1)
                    {
                        for (i = dataGridView3.Rows.Count - 2; i >= 0; i--)
                        {
                            
                            MaCT1 = dataGridView3.Rows[i].Cells["MaCTXuat"].Value.ToString();
                            SoKH1 = dataGridView3.Rows[i].Cells["SoKHXuat"].Value.ToString();
                            SoBB1 = dataGridView3.Rows[i].Cells["SoBBXuat"].Value.ToString();
                            SoXe1 = dataGridView3.Rows[i].Cells["SoXe"].Value.ToString();
                            SLXuat_d = double.Parse(dataGridView3.Rows[i].Cells["SoLuongXuat"].Value.ToString());
                            Ngay1 = dataGridView3.Rows[i].Cells["NgayInsertXuat"].Value.ToString();
                            GhiChu1 = dataGridView3.Rows[i].Cells["GhiChuoutput"].Value.ToString();
                            FSC1 = dataGridView3.Rows[i].Cells["FSCoutput"].Value.ToString();
                            flag1 = dataGridView3.Rows[i].Cells["flag_out"].Value.ToString();
                            UserNhap="";
                            UserXuat = lb_user.Text;
                            cmd.Parameters.AddWithValue("@MaCT", MaCT1);
                            cmd.Parameters.AddWithValue("@SoBB", SoBB1);
                            cmd.Parameters.AddWithValue("@SoXe", SoXe1);
                            cmd.Parameters.AddWithValue("@SoKH", SoKH1);
                            cmd.Parameters.AddWithValue("@SLNhap", "0");
                            cmd.Parameters.AddWithValue("@SLXuat", SLXuat_d);
                            cmd.Parameters.AddWithValue("@Ngay", Ngay1);
                            cmd.Parameters.AddWithValue("@FSC", FSC1);
                            cmd.Parameters.AddWithValue("@UserNhap",UserNhap);
                            cmd.Parameters.AddWithValue("@UserXuat", UserXuat);
                            cmd.Parameters.AddWithValue("@GhiChu", GhiChu1);
                            cmd.Parameters.AddWithValue("@flag", flag1);
                            cmd.Parameters.AddWithValue("@ID", SoKH1);
                            if(flag1=="2")
                                cmd.Parameters.AddWithValue("@SLTheoDoi", -SLXuat_d);
                            else
                                cmd.Parameters.AddWithValue("@SLTheoDoi", SLXuat_d);
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            //dataGridView1.Rows.RemoveAt(i);
                            //insert_XuatKTG = @"insert into dbo.KV1_KTG(MaCT,SoBB,SoKH,SLXuat,Ngay,UserXuat,GhiChu)values(N'" + MaCT1 + "',N'" + SoBB1 + "',N'" + SoKH1 + "'," + SLXuat_d + ",'" + Ngay1 + "',N'" + UserXuat + "',N'"+GhiChu1+"')";
                            //cmd.CommandText = insert_XuatKTG;
                            //MessageBox.Show(insert_NhapKTG);
                            //cmd.ExecuteNonQuery();
                            dataGridView3.Rows.RemoveAt(i);
                            
                        }
                        MessageBox.Show("Dữ Liệu được lưu thành công");
                        lb_TongXuat.Text = "0";
                        dta.reset_txt(groupPanel2);
                    }
                }
                #region
                /*if (rd_ChuyenNoiBo.Checked == true)
                {
                    if (dataGridView3.Rows.Count > 1)
                    {
                        for (i = dataGridView3.Rows.Count - 2; i >= 0; i--)
                        {
                            MaCTC = dataGridView3.Rows[i].Cells["MaCTChuyen"].Value.ToString();
                            MaCT1 = dataGridView3.Rows[i].Cells["MaCTXuat"].Value.ToString();
                            SoKH1 = dataGridView3.Rows[i].Cells["SoKHXuat"].Value.ToString();
                            SoBB1 = dataGridView3.Rows[i].Cells["SoBBXuat"].Value.ToString();
                            SLXuat_d = double.Parse(dataGridView3.Rows[i].Cells["SoLuongXuat"].Value.ToString());
                            Ngay1 = dataGridView3.Rows[i].Cells["NgayInsertXuat"].Value.ToString();
                            GhiChu1 = dataGridView3.Rows[i].Cells["GhiChuoutput"].Value.ToString();
                            UserXuat = lb_user.Text;
                            insert_XuatKTG = @"insert into dbo.KV1_KTG(MaCT,SoBB,SoKH,SLNhap,Ngay,UserNhap,GhiChu)values(N'" + MaCTC + "',N'" + SoBB1 + "',N'" + SoKH1 + "'," + SLXuat_d + ",'" + Ngay1 + "',N'" + UserXuat + "',N'"+GhiChu1+"')";
                            
                            cmd.CommandText = insert_XuatKTG;
                            //MessageBox.Show(insert_NhapKTG);
                            cmd.ExecuteNonQuery();
                            SLXuat_d = -SLXuat_d;
                            insert_XuatKTG = @"insert into dbo.KV1_KTG(MaCT,SoBB,SoKH,SLNhap,Ngay,UserNhap)values(N'" + MaCT1 + "',N'" + SoBB1 + "',N'" + SoKH1 + "'," + SLXuat_d + ",'" + Ngay1 + "',N'" + UserXuat + "')";
                            cmd.CommandText = insert_XuatKTG;
                            cmd.ExecuteNonQuery();
                            dataGridView3.Rows.RemoveAt(i);
                            
                        }
                        MessageBox.Show("Dữ Liệu được lưu thành công");
                        lb_TongXuat.Text = "0";
                        dta.reset_txt(groupPanel2);
                    }
                }*/
            #endregion

            catch (Exception ex)
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
                MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void bt_saveoutput_Click(object sender, EventArgs e)
        {
            save_output();
        }
        private void edit_output()
        {
           
                MessageBox.Show("Hiện tại chức năng này chưa hoạt động, Bạn có thể xóa dữ liệu sai, sau đó thêm mới");
                /*string datecheck;
                int date_count;
                Process prs = new Process();
                SqlConnection Conn = prs.Connect();
                SqlCommand cmd = new SqlCommand();
                DialogResult d = MessageBox.Show("Bạn có chắc muốn sửa Thông Tin có Số Serial " + lb_serialout.Text, "Thông Báo", MessageBoxButtons.OKCancel);
                MaCT1 = prs.substring_right(cb_MaCToutput.Text, child);
                Ngay1 = txt_Ngayoutput.Text;
                SoBB1 = txt_SoBBoutput.Text;
                SoKH1 = cb_SoKHoutput.Text;
                SLXuat_d = double.Parse(txt_SLoutput.Text);
                GhiChu1 = txt_GhiChuoutput.Text;
                string[] temp = Ngay1.Split('/');
                Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                Conn.Open();
                cmd.Connection = Conn;
                datecheck = @"select datediff(dd,NgayInsert,getdate()) from dbo.KV1_KTG where Serial='" + lb_serialout.Text + "'";
                if (d == DialogResult.OK)
                {
                    cmd.CommandText = datecheck;
                    date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1)
                    {

                        string update_XP = @"UPDATE [DataBase_ScansiaPacific].[dbo].[KV1_KTG] SET MaCT='" + MaCT1 + "',SoBB='" + SoBB1 + "',SoKH='" + SoKH1 + "',SLXuat=" + SLXuat_d + ",Ngay='" + Ngay1 + "',Useredit='"+lb_user.Text+"',GhiChu=N'"+GhiChu1+"' where Serial='" + lb_serialout.Text + "'";
                        // MessageBox.Show(update_XP);
                        cmd.CommandText = update_XP;
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("Chỉnh Sửa Thành Công");
                    }
                    else
                        MessageBox.Show("Không được phép chỉnh sửa");
                    Conn.Close();
                    cmd.Dispose();
                    for (int i = 0; i < dataGridView4.Rows.Count - 1; i++)
                    {
                        dataGridView4.Rows.RemoveAt(0);
                    }
                    prs.reset_txt(groupPanel2);
                    bt_editoutput.Enabled = false;
                    bt_deleteoutput.Enabled = false;
                    lb_serialout.Text = "0";
                }
            }
            catch (Exception ex)
            {
                string s = "The conversion of a char data type to a datetime";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Ngày Tháng Nhập chưa đúng"); }
                MessageBox.Show(ex.Message);
            }*/

        }
        private void Display_output(int i)
        {
            try
            {
                
                MaSP1 = dataGridView4.Rows[i].Cells["MaSP"].Value.ToString();
                
                TenSP1 = dataGridView4.Rows[i].Cells["TenSP"].Value.ToString();
                
                MaCT1 = dataGridView4.Rows[i].Cells["MaCT"].Value.ToString();
                
                TenCT1 = dataGridView4.Rows[i].Cells["TenChiTiet"].Value.ToString();
                
                cb_MaSPoutput.Text = TenSP1 + " - " + MaSP1;
                cb_MaCToutput.Text = TenCT1 + " - " + MaCT1;
                txt_SoBBoutput.Text = dataGridView4.Rows[i].Cells["SoBB"].Value.ToString();
                cb_SoKHoutput.Text = dataGridView4.Rows[i].Cells["SoKH"].Value.ToString();
                txt_SLoutput.Text= dataGridView4.Rows[i].Cells["SLXuat"].Value.ToString();
                txt_Ngayoutput.Text = dataGridView4.Rows[i].Cells["Ngay"].Value.ToString();
                lb_serialout.Text = dataGridView4.Rows[i].Cells["Serial"].Value.ToString();
                txt_GhiChuoutput.Text = dataGridView4.Rows[i].Cells["GhiChu"].Value.ToString();
                lb_SoKHoutput.Text = dataGridView4.Rows[i].Cells["SoKH"].Value.ToString();
                txt_SoXe.Text = dataGridView4.Rows[i].Cells["SoXe"].Value.ToString();
               // MessageBox.Show(lb_SoKHoutput.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void search_Output()
        {
            try
            {
                string search = null;
                Process prs = new Process();
                DataSet ds = new DataSet();
                if (cb_SoKHoutput.Text!="")
                {
                    search = @"select qry1.Serial,ChiTietSP.TenSP,ChiTietSP.MaSP,ChiTietSP.TenChiTiet,qry1.MaCT,qry1.SoBB,qry1.SoXe,qry1.SoKH,qry1.SLXuat,qry1.Ngay,qry1.GhiChu from
                           (select Serial,MaCT,SoBB,SoXe,SoKH,SLXuat,Ngay,GhiChu from dbo.KV1_KTG where SoKH='" + cb_SoKHoutput.Text + "' and SLXuat<>0) as qry1 inner join dbo.ChiTietSP on qry1.MaCT=ChiTietSP.MaChiTiet";
                }
                else
                {
                    if (cb_MaCToutput.Text!= "" && txt_Ngayoutput.Text != "")
                    {
                        MaCT1 = prs.substring_right(cb_MaCToutput.Text, child);
                        string[] temp = txt_Ngayoutput.Text.Split('/');
                        Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    }
                    if (MaCT1 == "")
                        MessageBox.Show("Mã CT không chính xác");
                    else
                    {
                        search = @"select qry1.Serial,ChiTietSP.TenSP,ChiTietSP.MaSP,ChiTietSP.TenChiTiet,qry1.MaCT,qry1.SoBB,qry1.SoKH,qry1.SLXuat,qry1.Ngay,qry1.GhiChu,isnull(qry1.SoXe,0) as SoXe from (select Serial,MaCT,SoBB,SoXe,SoKH,SLXuat,Ngay,GhiChu from dbo.KV1_KTG where MaCT='" + MaCT1 + "' and Ngay='" + Ngay1 + "' and SLXuat<>0) as qry1 inner join dbo.ChiTietSP on qry1.MaCT=ChiTietSP.MaChiTiet";
                    }
                }
               
                ds = prs.dts(search);
                if (ds.Tables[0].Rows.Count == 0)
                    MessageBox.Show("Không có dữ liệu, vui lòng kiểm tra lại thông tin tìm kiếm");
                else
                {
                    dataGridView4.DataSource = ds.Tables[0];
                    int k = dataGridView4.Rows.Count - 2;
                    //lb_TonKho.Text = "1000000";
                    Display_output(k);
                    
                    lb_indexout.Text = k.ToString();
                    bt_editoutput.Enabled = true;
                    bt_deleteoutput.Enabled = true;
                    
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_searchoutput_Click(object sender, EventArgs e)
        {
            search_Output();
            bt_insertoutput.Enabled = false;
            cb_SoKHoutput.Enabled = false;
            txt_SLoutput.Enabled = false;
        }

        private void bt_prevout_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView4.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_indexout.Text);
                    if (i > 0)
                    {
                        i--;
                        Display_output(i);
                        lb_indexout.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void bt_nextout_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView4.Rows.Count > 0)
                {
                    int i;
                    i = int.Parse(lb_indexout.Text);
                    if (i < dataGridView4.Rows.Count - 2)
                    {
                        i++;
                        Display_output(i);
                        lb_indexout.Text = i.ToString();
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void bt_editoutput_Click(object sender, EventArgs e)
        {
            edit_output();
        }

        private void bt_deleteoutput_Click(object sender, EventArgs e)
        {
            DialogResult d = MessageBox.Show("Bạn có chắc muốn xóa Thông Tin có Số Serial " + lb_serialout.Text, "Thông Báo", MessageBoxButtons.OKCancel);
            if (d == DialogResult.OK)
            {
                try
                {
                    string datecheck = @"select datediff(dd,NgayInsert,getdate()) from dbo.KV1_KTG where Serial='" + lb_serialout.Text + "'";
                    //string delete = @"delete from dbo.KV1_KTG where Serial='" + lb_serialout.Text + "'";
                    Data dta = new Data();
                    SqlConnection Conn = dta.Connect();
                    Conn.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = Conn;
                    cmd.CommandText = datecheck;
                    int date_count = int.Parse(cmd.ExecuteScalar().ToString());
                    if (date_count <= 1 || lb_user.Text == "administrator")
                    {
                        string Serial1 = lb_serialout.Text;
                        SLXuat_d = double.Parse(txt_SLoutput.Text);
                        SoKH1 = lb_SoKHoutput.Text;
                        cmd.CommandText = @"select flag from dbo.KV1_KTG where Serial="+Serial1;
                        flag1 = cmd.ExecuteScalar().ToString();
                        if (flag1 == "0")
                        {
                            cmd.CommandText = "Tran_Del_Up";
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@Serial", Serial1);
                            cmd.Parameters.AddWithValue("@ID", SoKH1);
                            cmd.Parameters.AddWithValue("@SLTheoDoi", SLXuat_d);
                            cmd.ExecuteNonQuery();
                            MessageBox.Show(SoKH1);
                            MessageBox.Show("Dữ liệu được xóa thành công");
                            lb_serialout.Text = "0";
                        }
                        else
                            MessageBox.Show("Dữ liệu này thuộc nhóm chuyển, Vui lòng liên hệ quản trị viên để xóa");
                    }
                    else
                    {
                        MessageBox.Show("Quá thời gian cho phép,Không được phép xóa");
                    }
                    Conn.Close();
                    cmd.Dispose();
                    for (int i = 0; i < dataGridView4.Rows.Count - 1; i++)
                    {
                        dataGridView4.Rows.RemoveAt(0);
                    }
                    dta.reset_txt(groupPanel2);
                    bt_deleteoutput.Enabled = false;
                    bt_editoutput.Enabled = false;

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        
        private void TonKho()
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cb_SoKHoutput.Items.Clear();
                cb_SoKHoutput.Text = "";
                Conn.Open();
                cmd.Connection = Conn;
                MaCT1 = prs.substring_right(cb_MaCToutput.Text, child);
               
                string TonKho = @"select TonKho from dbo.KTG_TK where MaCT='" + MaCT1 + "'";
                cmd.CommandText = TonKho;
                lb_TonKho.Text = cmd.ExecuteScalar().ToString();
                TonKho = @"select left(ID_KeHoach,5),ID from KH_TheoDoi where SLTheoDoi>0 and MaCT='" + MaCT1 + "' and ID_KeHoach like '%KV1X%'";
                Frp.cb1(cb_SoKHoutput, TonKho, 0, 1);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                lb_TonKho.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }

        }

        private void cb_MaCTXuat_SelectedIndexChanged(object sender, EventArgs e)
        {
            TonKho();
           
        }
        private void check_TonKho()
        {
            if (txt_SLoutput.Text != "")
            {
                SLXuat_d = double.Parse(txt_SLoutput.Text);
                TK_KTG = double.Parse(lb_TonKho.Text);
                if (SLXuat_d > TK_KTG)
                {
                    MessageBox.Show("SL xuất không vượt quá SL Tồn Kho");
                    txt_SLoutput.Text = "";
                    //txt_SLoutput.Focus();

                }
                else
                {
                    if (rd_ChuyenNoiBo.Checked == false)
                    {
                        TK_KTG = double.Parse(lb_SLKH.Text);
                        if (SLXuat_d > TK_KTG)
                        {
                            MessageBox.Show("SL xuất không vượt quá SL của KH");
                            txt_SLoutput.Text = "";
                            //txt_SLoutput.Focus();
                        }
                    }
                }
                
            }
           
        }
        private void search_TK()
        {
            try
            {
                string searchTK = null;
                Process prs = new Process();
                if (cb_MaSPTK.Text == "" && cb_MaCTTK.Text == "")
                {
                    searchTK = @"select TenSP,TenChiTiet,TonKho from dbo.KTG_TK";
                }
                if (cb_MaSPTK.Text != "" & cb_MaCTTK.Text == "")
                {
                    MaSP1 = prs.substring_right(cb_MaSPTK.Text, child);
                    searchTK = @"select TenSP,TenChiTiet,TonKho from dbo.KTG_TK where MaSP='" + MaSP1 + "'";
                }
                if (cb_MaSPTK.Text != "" & cb_MaCTTK.Text != "")
                {
                    MaCT1 = prs.substring_right(cb_MaCTTK.Text, child);
                    searchTK = @"select TenSP,TenChiTiet,TonKho from dbo.KTG_TK where MaCT='" + MaCT1 + "'";
                }
                DataSet ds = prs.dts(searchTK);
                if (ds.Tables[0].Rows.Count == 0)
                    MessageBox.Show("Không có dữ liệu");
                else
                {
                    dataGridView5.Rows.Clear();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        TenSP1 = ds.Tables[0].Rows[i]["TenSP"].ToString();
                        TenCT1 = ds.Tables[0].Rows[i]["TenChiTiet"].ToString();
                        SLNhap_d=double.Parse(ds.Tables[0].Rows[i]["TonKho"].ToString());
                        SLNhap1 = string.Format("{0:###,###,###}", SLNhap_d);
                        dataGridView5.Rows.Add(TenSP1, TenCT1, SLNhap1);
                    }
                    prs.formatcolordatagrid(dataGridView5);
                    ds.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void bt_searchTK_Click(object sender, EventArgs e)
        {
            search_TK();
        }

        private void cb_MaSPTK_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            MaSP1 = prs.substring_right(cb_MaSPTK.Text, child);
            cb_MaCTTK.Items.Clear();
            cb_MaCTTK.Text = "";
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSP1 + "'";
            Frp.cb1(cb_MaCTTK, sql_load, 1, 0);
        }
        private void report_XKTG()
        {
            try
            {
                if (txt_Ngayoutput.Text != "")
                {
                    Process prs = new Process();
                    Ngay1 = txt_Ngayoutput.Text;
                    string[] temp = Ngay1.Split('/');
                    Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string print_XKTG = @"select dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,dbo.ChiTietSP.LoaiGo,qry1.Serial as Serial_NK,qry1.SLXuat as SoLuong,(dbo.ChiTietSP.ChieuDaySC*dbo.ChiTietSP.ChieuRongSC*dbo.ChiTietSP.ChieuDaiSC)*qry1.SLXuat/1000000000 as SoKhoi,qry1.Ngay,left(qry1.SoKH,5) as MaKH
                                        from (select MaCT,Serial,SLXuat,SoKH,convert(varchar(5),NgayInsert,108) as Ngay from dbo.KV1_KTG";
                    if (cb_MaSPoutput.Text == "")
                    {
                        print_XKTG += @" where SLXuat <>0 and Ngay='" + Ngay1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet order by qry1.Serial";
                    }
                    else
                    {
                        if (cb_MaCToutput.Text == "")
                        {
                            MaSP1 = prs.substring_right(cb_MaSPoutput.Text, child);
                            print_XKTG += @" where SLXuat <>0 and Ngay='" + Ngay1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet where dbo.ChiTietSP.MaSP='" + MaSP1 + "' order by qry1.Serial";
                        }
                        else
                        {
                            MaCT1 = prs.substring_right(cb_MaCToutput.Text, child);
                            print_XKTG += @" where SLXuat <>0 and Ngay='" + Ngay1 + "' and MaCT='" + MaCT1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet order by qry1.Serial";
                        }
                    }
                    DataSet ds = prs.dts(print_XKTG);
                    Fr_Report FrR = new Fr_Report();
                    Ngay1 = txt_Ngayoutput.Text;
                    FrR.report_KV1XKTG(ds,Ngay1);
                    FrR.Show();
                    ds.Clear();

                }
                else
                    MessageBox.Show("Chọn ngày cần in");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void report_NKTG()
        {
            try
            {
                if (txt_Ngayinput.Text!="")
                {
                    Process prs = new Process();
                    Ngay1 = txt_Ngayinput.Text;
                    string[] temp = Ngay1.Split('/');
                    Ngay1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                   
                    string print_NKTG = @"select dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,dbo.ChiTietSP.LoaiGo,qry1.Serial as Serial_NK,qry1.SLNhap as SoLuong,(dbo.ChiTietSP.ChieuDaySC*dbo.ChiTietSP.ChieuRongSC*dbo.ChiTietSP.ChieuDaiSC)*qry1.SLNhap/1000000000 as SoKhoi,left(qry1.SoKH,5) as MaKeHoach,'' as DVT,qry1.Ngay
                                        from (select MaCT,Serial,SLNhap,SoKH,convert(varchar(5),NgayInsert,108) as Ngay from dbo.KV1_KTG";
                    if (cb_MaSPinput.Text == "")
                    {
                        print_NKTG += @" where SLNhap <>0 and Ngay='" + Ngay1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet where qry1.Ngay>='"+maskedTextBox1.Text+"' and qry1.Ngay<='"+maskedTextBox2.Text+"' order by qry1.Serial";
                       
                    }
                    else
                    {
                        if (cb_MaCTinput.Text == "")
                        {
                            MaSP1 = prs.substring_right(cb_MaSPinput.Text, child);
                            print_NKTG += @" where SLNhap <>0 and Ngay='" + Ngay1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet where dbo.ChiTietSP.MaSP='" + MaSP1 + "' and qry1.Ngay>='" + maskedTextBox1.Text + "' and qry1.Ngay<='" + maskedTextBox2.Text + "' order by qry1.Serial";
                           
                        }
                        else
                        {
                            MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                            print_NKTG += @" where SLNhap <>0 and Ngay='" + Ngay1 + "' and MaCT='" + MaCT1 + "') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet  where qry1.Ngay>='" + maskedTextBox1.Text + "' and qry1.Ngay<='" + maskedTextBox2.Text + "'  order by qry1.Serial";
                        }
                    }
                    DataSet ds = prs.dts(print_NKTG);
                    Fr_Report FrR = new Fr_Report();
                    Ngay1 = txt_Ngayinput.Text;
                    FrR.report_KV1NKTG(ds,Ngay1);
                    FrR.Show();
                    ds.Clear();

                }
                else
                    MessageBox.Show("Chọn ngày cần in");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_printNK_Click(object sender, EventArgs e)
        {
            report_NKTG();
        }

        private void bt_printXuat_Click(object sender, EventArgs e)
        {
            report_XKTG();
        }
        private DataSet report_search()
        {
            Process prs = new Process();
            DataSet ds = new DataSet();
            SqlConnection Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = Conn;
            cmd.CommandType = CommandType.StoredProcedure;
            string[] temp = txt_NgayStart.Text.Split('/');
            Ngay_start1 = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
            string[] temp2 = txt_Ngayend.Text.Split('/');
            Ngay_End1 = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
            if (chk_All.Checked == false)
            {
                if (cb_MaSPReport.Text == "")
                {
                    cmd.CommandText = "prc_KTG_TH";
                    cmd.Parameters.AddWithValue("@datebegin", Ngay_start1);
                    cmd.Parameters.AddWithValue("@dateend", Ngay_End1);
                }
                if (cb_MaSPReport.Text != "")
                {
                    MaSP1 = prs.substring_right(cb_MaSPReport.Text, child);
                    if (MaSP1 == "")
                        MessageBox.Show("Nhập sai thông tin SP");
                    else
                    {

                        cmd.CommandText = "prc_KTG_TH_SP";
                        cmd.Parameters.AddWithValue("@datebegin", Ngay_start1);
                        cmd.Parameters.AddWithValue("@dateend", Ngay_End1);
                        cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                        ds = prs.dts_sql(cmd);

                        /*Search = @"select qry2.MaCT,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,qry2.SLNhap,qry2.SLNhap/SoLuongCT as DBNhapPS,qry2.SLXuat,qry2.SLXuat/SoLuongCT as DBXuatPS,isnull(qry2.TonDau,0) as TonDau,qry2.TonPS,qry2.TonPS/SoLuongCT as DBTonPS,(qry2.TonPS+isnull(qry2.TonDau,0) )*Load_CTSP_Mua.ChieuDaySC*Load_CTSP_Mua.ChieuRongSC*Load_CTSP_Mua.ChieuDaiSC/1000000000 as SoKhoi
                                from (select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(TonDau) as TonDau,sum(TonPS)as TonPS 
                                from((select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(SLNhap)-sum(SLXuat) as TonPS,TonDau=0  from dbo.KV1_KTG
                                where Ngay>='" + Ngay_start1 + " 00:00:00' and Ngay<='" + Ngay_End1 + " 23:59:59' Group by MaCT) union all (select MaCT,SLNhap=0,SLXuat=0,TonPS=0,sum(SLNhap)-sum(SLXuat) as TonDau from dbo.KV1_KTG where Ngay<'" + Ngay_start1 + " 00:00:00' group by MaCT)) as qry1 group by MaCT) as qry2 inner join dbo.Load_CTSP_Mua on qry2.MaCT=Load_CTSP_Mua.MaChiTiet where Load_CTSP_Mua.MaSP='" + MaSP1 + "'";*/
                        //Test
                    }
                }
                ds = prs.dts_sql(cmd);
            }
            if (chk_All.Checked == true)
            {
                string_temp = @"select qry1.MaCT,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,qry1.SLNhap,qry1.SLNhap/SoLuongCT as DBNhapPS,qry1.SLXuat,qry1.SLXuat/SoLuongCT as DBXuatPS,isnull(qry2.SLChuyen,0) as SoLuong,qry1.TonDau,qry1.TonPS+isnull(qry2.SLChuyen,0) as TonPS,(qry1.TonPS+isnull(qry2.SLChuyen,0))/SoLuongCT as DBTonPS,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau) as SLTon,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau)/SoLuongCT as DBTon,(qry1.TonPS+isnull(qry2.SLChuyen,0)+qry1.TonDau)*Load_CTSP_Mua.ChieuDaySC*Load_CTSP_Mua.ChieuRongSC*Load_CTSP_Mua.ChieuDaiSC/1000000000 as SoKhoi
                                from (select qry11.MaCT,sum(qry11.SLNhap) as SLNhap,sum(qry11.SLXuat) as SLXuat,sum(qry11.TonDau) as TonDau,sum(qry11.TonPS) as TonPS 
                                from 
                                (select MaCT,SLNhap,SLXuat,SLNhap-SLXuat as TonPS,TonDau=0 from dbo.KV1_KTG
                                where flag=0 and Ngay>='"+Ngay_start1+"' and Ngay<='"+Ngay_End1+"'";
               string_temp +=@"                 union all
                                select MaCT,SLNhap=0,SLXuat=0,TonPS=0,SLNhap-SLXuat as TonDau from dbo.KV1_KTG where Ngay<'"+Ngay_start1+"')";
               string_temp +=@"                 as qry11 group by qry11.MaCT)as qry1 left join 
                                (select MaCT,sum(SLNhap) as SLChuyen from dbo.KV1_KTG where flag=2 and Ngay>='"+Ngay_start1+"' and Ngay<='"+Ngay_End1+"' group by MaCT) ";
               string_temp +=@"                 as qry2 on qry1.MaCT=qry2.MaCT inner join dbo.Load_CTSP_Mua on qry1.MaCT=Load_CTSP_Mua.MaChiTiet";
               ds = prs.dts(string_temp);
            }
           
            cmd.Dispose();
            Conn.Close();
            return ds;

        }

        private void bt_searchreport_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView6.Rows.Count > 1)
                {
                    dataGridView6.Rows.Clear();
                }
                Process prs = new Process();
                DataSet ds = new DataSet();
                //string Search = null;


                ds = report_search();
                if (ds.Tables[0].Rows.Count > 0)
                {
                  
                   for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        TenSP1 = ds.Tables[0].Rows[i]["TenSP"].ToString();
                        TenCT1 = ds.Tables[0].Rows[i]["TenChiTiet"].ToString();
                        SLNhap_d=double.Parse(ds.Tables[0].Rows[i]["SLNhap"].ToString());
                        SLNhap1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["DBNhapPS"].ToString());
                        SoBB1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["SLXuat"].ToString());
                        SLXuat1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["DBXuatPS"].ToString());
                        SoKH1=string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["SoLuong"].ToString());
                        SLChuyen1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["TonDau"].ToString());
                        GhiChu1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["TonPS"].ToString());
                        flag1 = string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["SLTon"].ToString());
                        UserNhap=string.Format("{0:###,###,###}", SLNhap_d);
                        SLNhap_d = double.Parse(ds.Tables[0].Rows[i]["DBTon"].ToString());
                        UserXuat=string.Format("{0:###,###,###}", SLNhap_d);
                        
                        dataGridView6.Rows.Add(TenSP1, TenCT1, SLNhap1, SoBB1, SLXuat1, SoKH1,SLChuyen1, GhiChu1, flag1, UserNhap, UserXuat);
                        
                        
                    }
                   //dataGridView6.DataSource = ds.Tables[0];
                   prs.formatcolordatagrid(dataGridView6);
                   
                   ds.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void bt_printreport_Click(object sender, EventArgs e)
        {
            try
            {
                
                DataSet ds = new DataSet();
                ds = report_search();
                Fr_Report FrR = new Fr_Report();
                Ngay_start1 =txt_NgayStart.Text;
                Ngay_End1 = " - " + txt_Ngayend.Text;
                FrR.report_KTGTH(ds,Ngay_start1,Ngay_End1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void ExportExcel(DataTable dt, string path,int rowStart,int columnStart)
        {

            Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
            ExcelObj.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlNormalLoad);
            Microsoft.Office.Interop.Excel.Sheets sheet = theWorkbook.Worksheets;
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheet.get_Item(1);
            //worksheet.Activate();
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //Chuyển dữ liệu từ DataTable vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    //DataRow dr = dt.Rows[r];
                    arr[r, c] = dr[c];
                }
            }

            //Thiết lập vùng điền dữ liệu
            

            int rowEnd = rowStart + dt.Rows.Count - 1;
            //int rowEnd = rowStart + dt.Columns.Count - 1;

            int columnEnd = columnStart + dt.Columns.Count - 1;
            //int columnEnd = dt.Rows.Count - 1 + columnStart;

            // Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowStart, columnStart];
            // Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[rowEnd, columnEnd];
            // Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;
            //range.Columns.AutoFit();
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //theWorkbook=theWorkbook.Open("C:\\test.xlsx");
            //worksheet = ("sheet1");
            //range.Activate();
            object misValue = System.Reflection.Missing.Value;
            theWorkbook.Close(true, misValue, misValue);
            ExcelObj.Quit();

        }

        private void bt_excelreport_Click(object sender, EventArgs e)
        {
            try
            {
                Process prs = new Process();
                string Search = null;
                if (MaSP1 != "")
                {
                    string[] temp = txt_NgayStart.Text.Split('/');
                    Ngay_start1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                    string[] temp2 = txt_Ngayend.Text.Split('/');
                    Ngay_End1 = temp2[1] + "/" + temp2[0] + "/" + temp2[2];

                    Search = @"SELECT [Serial],[MaCT],[SoBB],[SoXe],[SoKH],[SLNhap],[SLXuat],[Ngay],[NgayInsert],[GhiChu],[flag]
                                FROM [dbo].[KV1_KTG] where 
                                Ngay>='" +Ngay_start1+" 00:00:00' and Ngay<='"+Ngay_End1+" 23:59:59'";
               
                   
                }
                DataSet ds = prs.dts(Search);
                ExportExcel(ds.Tables[0],"C:\\Huynh\\KTG.xlsx",6,1);
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void Users(string Users)
        {
            lb_user.Text = Users;
        }

        private void FrKTG_KeyDown(object sender, KeyEventArgs ex)
        {
            if (ex.KeyData == Keys.Enter)
            {
                SendKeys.Send("{TAB}");
            }
            if (ex.KeyCode == Keys.F11)
            {
                
                bt_save_Click(null, null);
            }
            if (ex.KeyCode == Keys.F12)
            {
                bt_saveoutput_Click(null, null);
            }
            if (ex.KeyData == Keys.F5)
            {
                lnk_KHNK_LinkClicked(null, null);
            }
            if (ex.KeyData == Keys.F6)
            {
                lnk_KHXK_LinkClicked(null, null);
            }
            if (ex.KeyData == Keys.F4)
            {
                lnk_KHDP_LinkClicked(null, null);
            }
            if (ex.KeyData == Keys.F2)
            {
                NKLenPhieu();
            }
            if (ex.KeyData == Keys.F1)
            {
                NKNhapKho();
            }
            if (ex.KeyData == Keys.F3)
            {
                TheoDoiKH_LenPhieu();
            }
            if (ex.KeyData == Keys.F7)
            {
                try
                {
                    if (txt_NgayStart.Text == "" || txt_Ngayend.Text == "")
                        MessageBox.Show("Bạn nhập thông tin tìm kiếm ở tab BÁO CÁO, sau đó nhấn F7 để xem");
                    else
                    {
                        DataSet ds = new DataSet();
                        ds = TongHopNhapXuat();
                        Fr_Report FrR = new Fr_Report();
                        Ngay_start1 = txt_NgayStart.Text;
                        Ngay_End1 = " - " + txt_Ngayend.Text;
                        FrR.report_KTGTH_NhapXuat(ds, Ngay_start1, Ngay_End1);
                        FrR.Show();
                        ds.Clear();
                    }
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }
            if (ex.KeyData == Keys.F8)
            {
                Exportexcel_TonKho();
            }

        }

        private void rd_ChuyenNoiBo_CheckedChanged(object sender, EventArgs e)
        {
            //MessageBox.Show("Option xuất chuyển nội bộ sử dụng trong trường hợp kho chuyển từ CT A sang CT B");
            lb_MaCTChuyen.Visible = true;
            cb_CTChuyen.Visible = true;
            cb_SoKHoutput.Text = "CNB";
           
        }

        private void rd_GiaoSoChe_CheckedChanged(object sender, EventArgs e)
        {
            lb_MaCTChuyen.Visible = false;
            cb_CTChuyen.Visible = false;
          
        }

        private void lb_user_TextChanged(object sender, EventArgs e)
        {
            Check_User();
        }

       

        private void dataGridView1_DoubleClick_1(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 1)
            {
                try
                {
                    int r = dataGridView1.CurrentCell.RowIndex;
                    //MessageBox.Show(r.ToString());
                    Tong1 = double.Parse(lb_Tong.Text) - double.Parse(dataGridView1.Rows[r].Cells["SLNhap"].Value.ToString());
                    if (Tong1 > 999)
                        lb_Tong.Text = string.Format("{0:###,###}", Tong1);
                    else
                        lb_Tong.Text = Tong1.ToString();
                    dataGridView1.Rows.RemoveAt(r);
                    //MessageBox.Show("aaaa");
                    j = dataGridView1.Rows.Count - 1;
                    dataGridView1.CurrentCell = dataGridView1.Rows[j].Cells["MaCT"];
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
            if (dataGridView1.Rows.Count == 1)
                lb_Tong.Text = "0";
        }

        private void dataGridView3_DoubleClick_1(object sender, EventArgs e)
        {
            if (dataGridView3.Rows.Count > 1)
            {
                try
                {
                    int r = dataGridView3.CurrentCell.RowIndex;
                    //MessageBox.Show(r.ToString());
                    Tong1 = double.Parse(lb_TongXuat.Text) - double.Parse(dataGridView3.Rows[r].Cells["SoLuongXuat"].Value.ToString());
                    if (Tong1 > 999)
                        lb_TongXuat.Text = string.Format("{0:###,###}", Tong1);
                    else
                        lb_TongXuat.Text = Tong1.ToString();
                    dataGridView3.Rows.RemoveAt(r);
                    //MessageBox.Show("aaaa");
                    j = dataGridView3.Rows.Count - 1;
                    dataGridView3.CurrentCell = dataGridView3.Rows[j].Cells["MaCTXuat"];
                }
                catch (Exception ex)
                { MessageBox.Show(ex.Message); }
            }
            if (dataGridView3.Rows.Count == 1)
                lb_TongXuat.Text = "0";
        }

        private void txt_SLoutput_Leave(object sender, EventArgs e)
        {
            try
            {
                check_TonKho();
            }
            catch
            {
            }
        }

       

        private void cb_SoKHoutput_Leave(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            try
            {
                SoKH1 = prs.substring_right(cb_SoKHoutput.Text, child);
                string check_SL = null;
                check_SL = @"select SLTheoDoi from dbo.KH_TheoDoi where ID='" + SoKH1 + "'";
                cmd.CommandText = check_SL;
                cmd.Connection = Conn;
                lb_SLKH.Text = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                //MessageBox.Show(ex.Message);
                cb_SoKHoutput.Text = "";
                lb_SLKH.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void txt_SoKHinput_Leave(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Process prs = new Process();
            DataSet ds = new DataSet();
            try
            {
                SoKH1 = prs.substring_right(txt_SoKHinput.Text, child);
                MaSP1=prs.substring_right(cb_MaSPinput.Text,child);
                MaCT1=prs.substring_right(cb_MaCTinput.Text,child);
                GhiChu1=MaSP1+"KV1N"+MaCT1;
                string check_SL = null;
                check_SL = @"select qry1.SLTheoDoi as SLKH,isnull(qry2.SLDP,0) as SLDP
                            from
                            (select SLTheoDoi,MaCT from dbo.KH_TheoDoi where ID='"+SoKH1+"') as qry1";
                check_SL +=@"            left join
                            (select SLKH-SLTheoDoi as SLDP,MaCT from dbo.KH_TheoDoi where ID='"+GhiChu1+"') as qry2 on qry1.MaCT=qry2.MaCT";
              
               ds = prs.dts(check_SL);
               if (ds.Tables[0].Rows.Count > 0)
               {
                   lb_SLKHN.Text = ds.Tables[0].Rows[0]["SLKH"].ToString();
                   lb_KHDP.Text = ds.Tables[0].Rows[0]["SLDP"].ToString();
               }
               else
               {

                   MessageBox.Show("Vui lòng kiểm tra lại thông tin nhập \n");
                   lb_SLKHN.Text = "0";
                   lb_KHDP.Text = "0";
               }
                
            }
            catch(Exception ex)
            {
                MessageBox.Show("Xem lại Mã SP hoặc Mã CT",ex.Message);
                txt_SoKHinput.Text = "";
                lb_SLKHN.Text = "0";
                lb_KHDP.Text="0";
            }
            finally
            {
                
            }
        }

        private void cb_MaCTinput_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                txt_SoKHinput.Items.Clear();
                cb_SoKHoutput.Text = "";
                Conn.Open();
                cmd.Connection = Conn;
                MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                txt_SoKHinput.Items.Clear();
                txt_SoKHinput.Text = "";
                GhiChu1 = @"select left(ID_KeHoach,5),ID from KH_TheoDoi where SLTheoDoi>0 and MaCT='" + MaCT1 + "' and ID_KeHoach like '%KV1N%'";
                //MessageBox.Show(SLKHNhap);
                Frp.cb1(txt_SoKHinput, GhiChu1, 0, 1);
                lb_KHDP.Text = "0";
                lb_SLKHN.Text = "0";
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                lb_KHDP.Text = "0";
                lb_SLKHN.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void txt_SLinput_Leave(object sender, EventArgs e)
        {
            try
            {
                Tong1 = double.Parse(lb_SLKHN.Text);
                if (txt_SLinput.Text != "")
                {
                    SLNhap_d = double.Parse(txt_SLinput.Text);
                    if (SLNhap_d > Tong1)
                    {
                        MessageBox.Show("Số lượng nhập hiện tại đã vượt quá số lượng trong KH, \n Bạn có thể nhập phần dư qua KH Dự Phòng");
                        txt_SLinput.Text = "";
                        //txt_SLinput.Focus();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }

        private void lb_KHDP_DoubleClick(object sender, EventArgs e)
        {
            try
            {
                check = 0;
                if (dataGridView1.Rows.Count > 1)
                {
                    while (check < dataGridView1.Rows.Count - 1)
                    {
                        if (MaCT1 != dataGridView1.Rows[i].Cells["MaCT"].Value.ToString())
                            check++;
                        else
                            break;
                    }
                }
                if (dataGridView1.Rows.Count == 1 || check == dataGridView1.Rows.Count - 1)
                {
                    KH_DP = double.Parse(lb_KHDP.Text);
                    if (KH_DP > 0)
                    {

                        SLKH_N = double.Parse(lb_SLKHN.Text);
                        if (SLKH_N >= KH_DP)
                        {
                            SLNhap_d = double.Parse(KH_DP.ToString());
                            SLKH_N = SLKH_N - SLNhap_d;
                            lb_SLKHN.Text = SLKH_N.ToString();
                            lb_KHDP.Text = "0";
                            
                        }
                        else
                        {
                            SLNhap_d = double.Parse(SLKH_N.ToString());
                            KH_DP = KH_DP - SLKH_N;
                            lb_SLKHN.Text = "0";
                            lb_KHDP.Text = KH_DP.ToString();
                        }
                        txt_GhiChuinput.Text = "Chuyển từ dự phòng";

                        Process prs = new Process();
                        string KHDP1 = MaSP1 + "KV1N" + MaCT1;
                        TenSP1 = prs.substring_left(cb_MaSPinput.Text, child);
                        TenCT1 = prs.substring_left(cb_MaCTinput.Text, child);
                        MaCT1 = prs.substring_right(cb_MaCTinput.Text, child);
                        SoKH1 = prs.substring_right(txt_SoKHinput.Text, child);
                        if (TenSP1 == "" || MaCT1 == "" || txt_Ngayinput.Text == "" || SoKH1 == "")
                        {
                            MessageBox.Show("Thông Tin Nhập Không Chính Xác Hoặc Thiếu");
                        }


                        else
                        {
                            int i = cb_MaCTinput.Items.Count - 1;
                            while (i >= 0)
                            {
                                if (MaCT1 == prs.substring_right(cb_MaCTinput.Items[i].ToString(), child))
                                    break;
                                else
                                    i--;
                            }
                            if (i >= 0)
                            {
                                SoBB1 = txt_SoBBinput.Text.Trim();
                                //SL1 = txt_SLInput.Text.Trim();
                                Ngay1 = txt_Ngayinput.Text.Trim();
                                SLNhap1 = SLNhap_d.ToString();
                                string[] temp = txt_Ngayinput.Text.Split('/');
                                NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                                GhiChu1 = txt_GhiChuinput.Text;
                                dataGridView1.Rows.Add(TenSP1, MaCT1, TenCT1, SoBB1, SoKH1, SLNhap1, Ngay1, NgayInsert1, GhiChu1,"1");
                               // Tong1 = double.Parse(lb_Tong.Text);
                                //Tong1 += double.Parse(SLNhap1);
                                SLNhap1 = "-" + SLNhap1;
                                dataGridView1.Rows.Add(TenSP1, MaCT1, TenCT1, SoBB1, KHDP1, SLNhap1, Ngay1, NgayInsert1, GhiChu1,"1");
                                //Tong1 += double.Parse(SLNhap1);
                                //MessageBox.Show(Tong1.ToString());
                                int j = dataGridView1.Rows.Count - 1;
                                dataGridView1.CurrentCell = dataGridView1.Rows[j].Cells[0];
                                dataGridView1.FirstDisplayedCell = dataGridView1.Rows[j].Cells[0];
                                prs.formatcolordatagrid(dataGridView1);
                                //prs.reset_txt(groupPanel1);
                                txt_SLinput.Text = "";
                                cb_MaCTinput.Text = "";
                                txt_GhiChuinput.Text = "";
                                //Tong1 = double.Parse(lb_Tong.Text);

                                /*lb_Tong.Text = Tong1.ToString();
                                if (lb_Tong.Text != "0")
                                    lb_Tong.Text = string.Format("{0:###,###}", Tong1);*/

                                //lb_KHDP.Text = "0";
                                //lb_SLKHN.Text = "0";
                                cb_MaSPinput.Focus();
                                MessageBox.Show("Kết chuyển THÀNH CÔNG");
                            }
                            else
                                MessageBox.Show("Thông tin nhập không đúng, Việc kết chuyển sẽ không được thực hiện");
                        }
                    }
                }
                else
                    MessageBox.Show("Chi tiết này đã có ở DS phía dưới.");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Việc kết chuyển đã THẤT BẠI, bạn vui lòng kiểm tra lại thông tin nhập",ex.Message);
            }
        }

       

        private void FrKTG_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.Control)
            {
                /*if (e.KeyCode.Equals(Keys.N))
                {
                    rd_GiaoSoChe.Enabled = true;
                    rd_ChuyenNoiBo.Enabled = true;
                }*/
                if (e.KeyCode.Equals(Keys.P))
                {
                    try
                    {
                        Data dta = new Data();
                        SqlConnection Conn = new SqlConnection();
                        SqlCommand cmd = new SqlCommand();
                        Conn = dta.Connect();
                        Conn.Open();
                        cmd.Connection = Conn;
                        SoBB1 = @"SELECT     max(Serial) FROM  dbo.KV1_KTG where SLXuat>0";
                        cmd.CommandText = SoBB1;
                        SoBB1 = cmd.ExecuteScalar().ToString();
                        print_phieupallet(SoBB1);
                        Conn.Close();
                        cmd.Dispose();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
                if(e.KeyCode.Equals(Keys.T))
                {
                    linkLabel1.Visible=true;
                }
                if(e.KeyCode.Equals(Keys.G))
                    
                {
                    maskedTextBox1.Visible=true;
                    maskedTextBox2.Visible=true;
                }
               
            }
        }

        private void lnk_KHNK_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string selectKH = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,KV1_SLNhap.NgayXP,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,KH_TheoDoi.SLKH as SoLuong,isnull(KV1_SLNhap.SLNhap,0) as SLXuat,KH_TheoDoi.SLKH-isnull(KV1_SLNhap.SLNhap,0) as SLTon 
                                from (select distinct(ID_KeHoach) as SoKH from dbo.KH_TheoDoi where SLTheoDoi>0 and ID_KeHoach like '%KV1N%') as KH_Chon inner join dbo.KH_TheoDoi on KH_TheoDoi.ID_KeHoach=KH_Chon.SoKH
                                left join (select KV1_KTG.SoKH,sum(SLNhap) as SLNhap,max(NgayInsert) as NgayXP from dbo.KV1_KTG group by SoKH) as KV1_SLNhap on KV1_SLNhap.SoKH=KH_TheoDoi.ID 
                                inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT";
            Process prs = new Process();
            DataSet ds = prs.dts(selectKH);
            Fr_Report FrR = new Fr_Report();
            //Ngay1 = txt_Ngayinput.Text;
            FrR.report_TDKH_NKTG(ds);
            FrR.Show();
            ds.Clear();
        }

        private void lnk_KHXK_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string selectKH = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,KV1_SLXuat.NgayXP,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,KH_TheoDoi.SLKH as SoLuong,isnull(KV1_SLXuat.SLXuat,0) as SLXuat,KH_TheoDoi.SLKH-isnull(KV1_SLXuat.SLXuat,0) as SLTon 
                                from (select distinct(ID_KeHoach) as SoKH from dbo.KH_TheoDoi where SLTheoDoi>0 and ID_KeHoach like '%KV1X%') as KH_Chon inner join dbo.KH_TheoDoi on KH_TheoDoi.ID_KeHoach=KH_Chon.SoKH
                                left join (select KV1_KTG.SoKH,sum(SLXuat) as SLXuat,max(NgayInsert) as NgayXP from dbo.KV1_KTG group by SoKH) as KV1_SLXuat on KV1_SLXuat.SoKH=KH_TheoDoi.ID 
                                inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT order by TenSP,KH_TheoDoi.ID_KeHoach";
            Process prs = new Process();
            DataSet ds = prs.dts(selectKH);
            Fr_Report FrR = new Fr_Report();
            //Ngay1 = txt_Ngayinput.Text;
            FrR.report_TDKH_XKTG(ds);
            FrR.Show();
            ds.Clear();
        }

        private void lnk_KHDP_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            string selectKH = @"select ChiTietSP.TenSP,ChiTietSP.TenChiTiet,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,KH_TheoDoi.SLKH as SoLuong,SLKH-KH_TheoDoi.SLTheoDoi as SLXuat,SLTheoDoi as SLTon,(SLKH-KH_TheoDoi.SLTheoDoi)/SLKH as SLNhap
                                from dbo.KH_TheoDoi inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT  where DuPhong='1' and ID_KeHoach like '%KV1N%'";
            Process prs = new Process();
            DataSet ds = prs.dts(selectKH);
            Fr_Report FrR = new Fr_Report();
            //Ngay1 = txt_Ngayinput.Text;
            FrR.report_TDKH_DPKTG(ds);
            FrR.Show();
            ds.Clear();
        }

        private void bt_resetinput_Click(object sender, EventArgs e)
        {
            cb_MaSPinput.Text = "";
            cb_MaCTinput.Text = "";
            txt_SoBBinput.Text = "";
            txt_SoKHinput.Text = "";
            txt_SoKHinput.Text = "";
            txt_GhiChuinput.Text = "";
            bt_insert.Enabled = true;
            lb_serialinput.Text = "0";
            bt_delete.Enabled = false;
            bt_edit.Enabled = false;
        }

        private void bt_resetoutput_Click(object sender, EventArgs e)
        {
            cb_MaSPoutput.Text = "";
            cb_MaCToutput.Text = "";
            cb_SoKHoutput.Text = "";
            txt_SoBBoutput.Text = "";
            txt_SLoutput.Text = "";
            txt_GhiChuoutput.Text = "";
            bt_insertoutput.Enabled = true;
            lb_serialout.Text = "0";
            bt_editoutput.Enabled = false;
            bt_deleteoutput.Enabled = false;
            cb_SoKHoutput.Enabled = true;
            txt_SLoutput.Enabled = true;
        }
        private void print_phieupallet(string Serial1)
        {
            try
            {
                GhiChu1 = @"SELECT     KV1.Serial as Serial_NK, dbo.ChiTietSP.MaSP, dbo.ChiTietSP.TenSP,dbo.ChiTietSP.MaChiTiet, dbo.ChiTietSP.TenChiTiet, KV1.SoKH,dbo.KH_TheoDoi.SLKH as SoLuong, KV1.SLXuat, KV1.NgayLP,KV1.Ngay,left(KV1.SoKH,5) as MaKH,KV1.SoXe,KV1.SoPhieu,
                      convert(char(4),dbo.ChiTietSP.ChieuDaySC)+'x'+ convert(char(4),dbo.ChiTietSP.ChieuRongSC)+'x'+ convert(char(6),dbo.ChiTietSP.ChieuDaiSC) as AmDo, 
                        convert(varchar(4),dbo.ChiTietSP.ChieuDayTC)+' x'+ convert(varchar(4),dbo.ChiTietSP.ChieuRongTC)+' x'+ 
                        convert(varchar(6),dbo.ChiTietSP.ChieuDaiTC) as DVT,PhieuPallet.Buoc as SoFax,PhieuPallet.NangSuat as ChieuDai,PhieuPallet.YeuCau as NoiNhan,dbo.ChiTietSP.LoaiGo,KV1.FCS,KV1.NguonGoc

                        from
                        (SELECT     Serial, MaCT, SoKH, SLXuat,SoBB as SoPhieu,SoXe, convert(varchar(10),dbo.KV1_KTG.NgayInsert,103)as NgayLP,convert(varchar(5),dbo.KV1_KTG.NgayInsert,108) as Ngay,GhiChu as FCS,FSC as NguonGoc
                        FROM         dbo.KV1_KTG where Serial="+Serial1+") as KV1 inner join dbo.KH_TheoDoi on KV1.SoKH=dbo.KH_TheoDoi.ID INNER JOIN dbo.ChiTietSP ON KV1.MaCT = dbo.ChiTietSP.MaChiTiet inner join dbo.PhieuPallet on KV1.MaCT=PhieuPallet.MaCT order by PhieuPallet.STT";

                Process prs = new Process();
                DataSet ds = prs.dts(GhiChu1);
                Fr_Report FrR = new Fr_Report();
                Ngay1 = txt_Ngayinput.Text;
                FrR.report_KTG_Pallet(ds);
                FrR.Show();
                ds.Clear();
                lb_serial_PhieuPallet.Visible = false;
                txt_PhieuPallet.Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void lik_PhieuPallet_Click(object sender, EventArgs e)
        {
            lb_serial_PhieuPallet.Visible = true;
            txt_PhieuPallet.Visible = true;
        }

        private void txt_PhieuPallet_Leave(object sender, EventArgs e)
        {
            if (txt_PhieuPallet.Text != "")
            {
                print_phieupallet(txt_PhieuPallet.Text);
            }
        }

      

        private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            GhiChu1 = @"select GroupUser from dbo.Users where UsersName='"+lb_user.Text+"'";
            SqlCommand cmd = new SqlCommand();
            SqlConnection Conn = new SqlConnection();
            Process prs = new Process();
            Conn = prs.Connect();
            Conn.Open();
            cmd.Connection = Conn;
            try
            {
                cmd.CommandText = GhiChu1;
                UserNhap = cmd.ExecuteScalar().ToString();
                if (UserNhap == "nhaplieu" || UserNhap == "admin")
                {
                    Fr_Transport Fr = new Fr_Transport();
                    Fr.Show();
                    Fr.Users(lb_user.Text);
                    linkLabel1.Visible = false;
                }
                else
                {
                    MessageBox.Show("Bạn không được phép sử dụng chức năng này");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }
        private void NKLenPhieu()
        {
            try
            {
                string[] temp = txt_Ngayoutput.Text.Split('/');
                NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                GhiChu1 = @"select Load_CTSP_Mua.LoaiGo,left(qry1.SoKH,5) as MaKH,dbo.KH_TheoDoi.SLKH as SoLuong,dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.TenChiTiet,qry1.SoKH,qry1.SLXuat,qry1.Ngay,qry1.NgayLP,qry1.SoBB as Serial_NK,(dbo.Load_CTSP_Mua.ChieuDaySC*dbo.Load_CTSP_Mua.ChieuRongSC*dbo.Load_CTSP_Mua.ChieuDaiSC)*qry1.SLXuat/1000000000 as SoKhoi,qry1.SoXe
                        from
                        (select Serial,MaCT,SoKH,SoXe,SLXuat,convert(varchar(10),NgayInsert,103) as Ngay,convert(varchar(5),NgayInsert,108) as NgayLP,SoBB from dbo.KV1_KTG where SoKH like'%KV1X%' and Ngay = '" + NgayInsert1 + "') as qry1 inner join dbo.KH_TheoDoi on dbo.KH_TheoDoi.ID=qry1.SoKH inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT order by Serial";
                Process prs = new Process();
                DataSet ds = prs.dts(GhiChu1);
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayinput.Text;
                FrR.report_NKPhieuPallet(ds);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //lb_serial_PhieuPallet.Visible = false;
            //txt_PhieuPallet.Visible = false;
        }
        private void NKNhapKho()
        {
            try
            {
                string[] temp = txt_Ngayinput.Text.Split('/');
                NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                GhiChu1 = @"select Load_CTSP_Mua.LoaiGo,left(qry1.SoKH,5) as MaKH,dbo.KH_TheoDoi.SLKH as SoLuong,dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.TenChiTiet,qry1.SoKH,qry1.SLNhap,qry1.Ngay,qry1.NgayLP,qry1.Serial as Serial_NK,(dbo.Load_CTSP_Mua.ChieuDaySC*dbo.Load_CTSP_Mua.ChieuRongSC*dbo.Load_CTSP_Mua.ChieuDaiSC)*qry1.SLNhap/1000000000 as SoKhoi,qry1.GhiChu as SoXe
                        from
                        (select Serial,MaCT,SoKH,GhiChu,SLNhap,convert(varchar(10),NgayInsert,103) as Ngay,convert(varchar(5),NgayInsert,108) as NgayLP from dbo.KV1_KTG where SoKH like'%KV1N%' and Ngay = '" + NgayInsert1 + "') as qry1 inner join dbo.KH_TheoDoi on dbo.KH_TheoDoi.ID=qry1.SoKH inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=qry1.MaCT order by Serial";
                Process prs = new Process();
                DataSet ds = prs.dts(GhiChu1);
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayinput.Text;
                NgayInsert1 = txt_Ngayinput.Text;
                FrR.report_NKNhapKTG(ds,"Ngày: "+NgayInsert1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //lb_serial_PhieuPallet.Visible = false;
            //txt_PhieuPallet.Visible = false;
        }
        private DataTable dta(int n)
        {
            int j = 0;
            string colum = null;
            DataTable dt = new DataTable();
            while (j < n)
            {
                colum = j.ToString();
                dt.Columns.Add(j.ToString(), typeof(string));
                j++;
            }
            return dt;
        }
        private DataTable report(string sql_load)
        {
            int j = 0, k = 0, i = 0;
            string columnname;
            Process prs = new Process();
            DataTable dt = new DataTable();
            /*SqlConnection Conn = prs.Connect();
           
            SqlCommand cmd = new SqlCommand();*/

            try
            {
 
                DataSet ds = prs.dts(sql_load);
                int dsrows = ds.Tables[0].Rows.Count;
                //int rowdata = int.Parse(cmd.ExecuteScalar().ToString());
                int columndata = 80;
                dt = dta(columndata);
                //MessageBox.Show(dt.Columns.Count.ToString());
                int count = 0;
                while (i < dsrows)
                {
                    DataRow dtrow = dt.NewRow();
                    j = 0;
                    columnname = j.ToString();
                    dtrow[columnname] = ds.Tables[0].Rows[count][0].ToString();

                    j++;
                    for (k = count; k < dsrows; k++)
                    {
                        if (k < dsrows - 1)
                        {

                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k + 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                break;
                            }
                        }
                        if (k == dsrows - 1)
                        {
                            if (ds.Tables[0].Rows[k][0].ToString() == ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }
                            }
                            if (ds.Tables[0].Rows[k][0].ToString() != ds.Tables[0].Rows[k - 1][0].ToString())
                            {
                                for (int m = 1; m < ds.Tables[0].Columns.Count; m++)
                                {
                                    columnname = j.ToString();
                                    dtrow[columnname] = ds.Tables[0].Rows[k][m].ToString();
                                    //MessageBox.Show(dtrow["4"].ToString());
                                    j++;
                                }

                                //MessageBox.Show("No Khac Rui Kia");
                                while (j < columndata)
                                {
                                    //
                                    columnname = j.ToString();
                                    dtrow[columnname] = "0";
                                    j++;
                                }
                                count = k + 1;
                                dt.Rows.Add(dtrow);
                                //MessageBox.Show(dt.Rows[0][18].ToString());
                                j = 0;
                                i = dsrows-1;
                                break;
                            }
                        }
                    }

                    i++;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return dt;
        }
        private void TheoDoiKH_LenPhieu()
        {
            if (cb_MaSPReport.Text == "")
                MessageBox.Show("Bạn cần nhập thông tin sản phẩm ở Tab BÁO CÁO, sau đó nhấn F3 để xem");
            else
            {
                Process prs = new Process();
                MaSP1 = prs.substring_right(cb_MaSPReport.Text, child);
                GhiChu1 = @"select qry.MaCT,qry.MaKH,qry.NgayXP,qry.SoLuong,qry.SLXuat,qry.ChenhLech from
                        (select ChiTietSP.MaSP+ChiTietSP.TenChiTiet as MaCT,KV1_SLXuat.NgayXP,left(KH_TheoDoi.ID_KeHoach,5) as MaKH,KH_TheoDoi.SLKH as SoLuong,isnull(KV1_SLXuat.SLXuat,0) as SLXuat,KH_TheoDoi.SLKH-isnull(KV1_SLXuat.SLXuat,0) as ChenhLech
                        from (select distinct(ID_KeHoach) as SoKH from dbo.KH_TheoDoi where SLTheoDoi>0 and ID_KeHoach like '%KV1X%') as KH_Chon inner join dbo.KH_TheoDoi on KH_TheoDoi.ID_KeHoach=KH_Chon.SoKH
                        left join (select KV1_KTG.SoKH,sum(SLXuat) as SLXuat,max(NgayInsert) as NgayXP from dbo.KV1_KTG group by SoKH) as KV1_SLXuat on KV1_SLXuat.SoKH=KH_TheoDoi.ID 
                        inner join ChiTietSP on ChiTietSP.MaChiTiet=KH_TheoDoi.MaCT 
                        where ChiTietSP.MaSP='"+MaSP1+"'";

                GhiChu1 +=@"        union all 
                        select 'zzzzz' as MaCT,'01/01/2013' as NgayXP,'99999' as MaKH,SoLuong=0,SLXuat=0,ChenhLech=0) as qry
                        order by MaCT,MaKH";
                //MessageBox.Show(GhiChu1);
                //dataGridView6.DataSource = report(GhiChu1);
                DataTable dt = report(GhiChu1);
                //MessageBox.Show(dt.Rows.Count.ToString());
                ExportExcel(dt, "C:\\Huynh\\KV1_KTGLP.xls", 6, 2);
                dt.Clear();
            }

        }

        private DataSet TongHopNhapXuat()
        {
            Process prs = new Process();
            DataSet ds = new DataSet();
            SqlConnection Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = Conn;
            cmd.CommandType = CommandType.StoredProcedure;
            string[] temp = txt_NgayStart.Text.Split('/');
            Ngay_start1 = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
            string[] temp2 = txt_Ngayend.Text.Split('/');
            Ngay_End1 = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
            cmd.CommandText = "prc_KTG_NXTH";
            if (cb_MaSPReport.Text == "")
            {
                
                cmd.Parameters.AddWithValue("@datebegin", Ngay_start1);
                cmd.Parameters.AddWithValue("@dateend", Ngay_End1);
                cmd.Parameters.AddWithValue("@MaSP", "0");

            }
            if (cb_MaSPReport.Text != "")
            {
                MaSP1 = prs.substring_right(cb_MaSPReport.Text, child);
                if (MaSP1 == "")
                    MessageBox.Show("Nhập sai thông tin SP");
                else
                {

                    
                    cmd.Parameters.AddWithValue("@datebegin", Ngay_start1);
                    cmd.Parameters.AddWithValue("@dateend", Ngay_End1);
                    cmd.Parameters.AddWithValue("@MaSP", MaSP1);
                    ds = prs.dts_sql(cmd);

                    /*Search = @"select qry2.MaCT,Load_CTSP_Mua.TenSP,Load_CTSP_Mua.TenChiTiet,Load_CTSP_Mua.SoLuongCT,qry2.SLNhap,qry2.SLNhap/SoLuongCT as DBNhapPS,qry2.SLXuat,qry2.SLXuat/SoLuongCT as DBXuatPS,isnull(qry2.TonDau,0) as TonDau,qry2.TonPS,qry2.TonPS/SoLuongCT as DBTonPS,(qry2.TonPS+isnull(qry2.TonDau,0) )*Load_CTSP_Mua.ChieuDaySC*Load_CTSP_Mua.ChieuRongSC*Load_CTSP_Mua.ChieuDaiSC/1000000000 as SoKhoi
                            from (select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(TonDau) as TonDau,sum(TonPS)as TonPS 
                            from((select MaCT,sum(SLNhap) as SLNhap,sum(SLXuat) as SLXuat,sum(SLNhap)-sum(SLXuat) as TonPS,TonDau=0  from dbo.KV1_KTG
                            where Ngay>='" + Ngay_start1 + " 00:00:00' and Ngay<='" + Ngay_End1 + " 23:59:59' Group by MaCT) union all (select MaCT,SLNhap=0,SLXuat=0,TonPS=0,sum(SLNhap)-sum(SLXuat) as TonDau from dbo.KV1_KTG where Ngay<'" + Ngay_start1 + " 00:00:00' group by MaCT)) as qry1 group by MaCT) as qry2 inner join dbo.Load_CTSP_Mua on qry2.MaCT=Load_CTSP_Mua.MaChiTiet where Load_CTSP_Mua.MaSP='" + MaSP1 + "'";*/
                    //Test



                }
            }
            ds = prs.dts_sql(cmd);
            cmd.Dispose();
            return ds;
        }

        private void txt_SoBBoutput_Leave(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SoBBoutput);
        }

        private void cb_MaSP_C_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();

            MaSP1 = prs.substring_right(cb_MaSP_C.Text, child);
            cb_MaCT_C.Items.Clear();

            cb_MaCT_C.Text = "";
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSP1 + "'";
            //Data dta = new Data();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand(sql_load, Conn);
            try
            {
                Conn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb_MaCT_C.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    //cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());
                    
                }
                Conn.Close();
                cmd.Dispose();
                rd.Close();
            }
            catch (Exception ex)
            { MessageBox.Show("Lỗi load Combobox " + ex.Message); }
            
        }

        private void cb_MaSP_N_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();

            MaSP1 = prs.substring_right(cb_MaSP_N.Text, child);
            cb_MaCT_N.Items.Clear();

            cb_MaCT_N.Text = "";
            string sql_load = @"select MaChiTiet,TenChiTiet from dbo.ChiTietSP where MaSP='" + MaSP1 + "'";
            //Data dta = new Data();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand(sql_load, Conn);
            try
            {
                Conn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    cb_MaCT_N.Items.Add(rd[1].ToString() + " - " + rd[0].ToString());
                    //cb.Items.Add(rd[column1].ToString() + " - " + rd[column2].ToString());

                }
                Conn.Close();
                cmd.Dispose();
                rd.Close();
            }
            catch (Exception ex)
            { MessageBox.Show("Lỗi load Combobox " + ex.Message); }
        }

        private void cb_MaCT_C_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cb_SoKH_C.Items.Clear();
                cb_SoKH_C.Text = "";
                Conn.Open();
                cmd.Connection = Conn;
                MaCT1 = prs.substring_right(cb_MaCT_C.Text, child);
                string TonKho = @"select TonKho from dbo.KTG_TK where MaCT='" + MaCT1 + "'";
                cmd.CommandText = TonKho;
                lb_TonKho_C.Text = cmd.ExecuteScalar().ToString();
                TonKho = @"select left(ID_KeHoach,5),ID from KH_TheoDoi where MaCT='" + MaCT1 + "' and ID_KeHoach like '%KV1N%'";
                Frp.cb1(cb_SoKH_C, TonKho, 0, 1);
            }
            catch
            {
                //MessageBox.Show(ex.Message);
                lb_TonKho.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void cb_MaCT_N_SelectedIndexChanged(object sender, EventArgs e)
        {
            Process prs = new Process();
            FormProcess Frp = new FormProcess();
            SqlConnection Conn = prs.Connect();
            SqlCommand cmd = new SqlCommand();
            try
            {
                cb_SoKH_N.Items.Clear();
                cb_SoKH_N.Text = "";
                Conn.Open();
                cmd.Connection = Conn;
                MaCT1 = prs.substring_right(cb_MaCT_N.Text, child);
                string TonKho = @"select TonKho from dbo.KTG_TK where MaCT='" + MaCT1 + "'";
                cmd.CommandText = TonKho;
                //lb_TonKho_N.Text = cmd.ExecuteScalar().ToString();
                TonKho = @"select left(ID_KeHoach,5),ID from KH_TheoDoi where MaCT='" + MaCT1 + "' and ID_KeHoach like '%KV1N%'";
                Frp.cb1(cb_SoKH_N, TonKho, 0, 1);
            }
            catch
            {
                
                lb_TonKho.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void cb_SoKH_C_Leave(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            try
            {
                SoKH1 = prs.substring_right(cb_SoKH_C.Text, child);
                string check_SL = null;
                check_SL = @"select SLKH-SLTheoDoi from dbo.KH_TheoDoi where ID='" + SoKH1 + "'";
                cmd.CommandText = check_SL;
                cmd.Connection = Conn;
                lb_KHC.Text = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                //MessageBox.Show(ex.Message);
                cb_SoKH_C.Text = "";
                lb_KHC.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void cb_SoKH_N_Leave(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Process prs = new Process();
            SqlConnection Conn = prs.Connect();
            Conn.Open();
            SqlCommand cmd = new SqlCommand();
            try
            {
                SoKH1 = prs.substring_right(cb_SoKH_N.Text, child);
                string check_SL = null;
                check_SL = @"select SLTheoDoi from dbo.KH_TheoDoi where ID='" + SoKH1 + "'";
                cmd.CommandText = check_SL;
                cmd.Connection = Conn;
                lb_KHN.Text = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                //MessageBox.Show(ex.Message);
                cb_SoKH_N.Text = "";
                lb_KHN.Text = "0";
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void txt_SLChuyen_TextChanged(object sender, EventArgs e)
        {
            FormProcess Frp = new FormProcess();
            Frp.checktext_number(txt_SLChuyen);
        }

        private void txt_SLChuyen_Leave(object sender, EventArgs e)
        {
            try
            {
                if (txt_SLChuyen.Text != "")
                {
                    SLNhap_d = double.Parse(txt_SLChuyen.Text);
                    SLKH_N = double.Parse(lb_KHN.Text);
                    double SLKH_C = double.Parse(lb_KHC.Text);
                    TK_KTG = double.Parse(lb_TonKho_C.Text);
                    if (SLNhap_d > SLKH_N||SLNhap_d>SLKH_C||SLNhap_d>TK_KTG)
                    {
                       
                        MessageBox.Show("Số lượng cần chuyển không được vượt quá " + SLKH_N.ToString()+" hoặc "+SLKH_C.ToString()+" hoặc "+TK_KTG.ToString());
                        txt_SLChuyen.Text = "";
                    }
                   
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void add_grid_Chuyen()
        {
            string TenSP2, TenCT2, MaCT2,SoKH2;
            try
            {
                Process prs = new Process();
                TenSP1 = prs.substring_left(cb_MaSP_C.Text, child);
                TenCT1 = prs.substring_left(cb_MaCT_C.Text, child);
                MaCT1 = prs.substring_right(cb_MaCT_C.Text, child);
                SoKH1 = prs.substring_right(cb_SoKH_C.Text, child);
                TenSP2 = prs.substring_left(cb_MaSP_N.Text, child);
                TenCT2 = prs.substring_left(cb_MaCT_N.Text, child);
                MaCT2 = prs.substring_right(cb_MaCT_N.Text, child);
                SoKH2 = prs.substring_right(cb_SoKH_N.Text, child);
                
                if (TenSP1 == "" || MaCT1 == "" || txt_SLChuyen.Text == "" || SoKH1 == ""||TenSP2==""||MaCT2==""||SoKH2=="")
                {
                    MessageBox.Show("Thông Tin Nhập Không Chính Xác Hoặc Thiếu");
                }
                else
                {
                    i = 0;
                    if (dataGridView7.Rows.Count > 1)
                    {
                        while (i < dataGridView7.Rows.Count - 1)
                        {
                            if (MaCT1 != dataGridView7.Rows[i].Cells["MaCT_C"].Value.ToString())
                                i++;
                            else
                                break;
                        }
                    }
                    if (dataGridView7.Rows.Count == 1 || i == dataGridView7.Rows.Count - 1)
                    {
                        k = cb_MaCT_C.Items.Count - 1;
                        while (k >= 0)
                        {

                            if (MaCT1 == prs.substring_right(cb_MaCT_C.Items[k].ToString(), child))
                                break;
                            else
                                k--;
                        }
                        j = cb_SoKH_C.Items.Count - 1;
                        //MessageBox.Show(j.ToString());
                        while (j >= 0)
                        {
                            if (SoKH1 == prs.substring_right(cb_SoKH_C.Items[j].ToString(), child))
                                break;
                            else
                                j--;
                        }
                        if (k >= 0 && j >= 0)
                        {
                            //SoBB1 = txt_SoBBinput.Text.Trim();
                            //SL1 = txt_SLInput.Text.Trim();
                            Ngay1 = txt_Ngay_C.Text.Trim();
                            SLNhap1 = "-"+txt_SLChuyen.Text;
                            string[] temp = txt_Ngay_C.Text.Split('/');
                            NgayInsert1 = temp[1] + "/" + temp[0] + "/" + temp[2];
                            
                            dataGridView7.Rows.Add(TenSP1, MaCT1, TenCT1, SoKH1, SLNhap1, Ngay1, NgayInsert1,"2");
                            SLNhap1 = txt_SLChuyen.Text;
                            dataGridView7.Rows.Add(TenSP2, MaCT2, TenCT2, SoKH2, SLNhap1, Ngay1, NgayInsert1, "2");
                            j = dataGridView7.Rows.Count - 1;
                            dataGridView7.CurrentCell = dataGridView7.Rows[j].Cells[0];
                            dataGridView7.FirstDisplayedCell = dataGridView7.Rows[j].Cells[0];
                            prs.formatcolordatagrid(dataGridView7);
                            //prs.reset_txt(groupPanel1);
                            txt_SLChuyen.Text = "";
                            lb_KHC.Text = "0";
                            lb_KHN.Text = "0";
                            cb_MaSP_C.Focus();
                            cb_SoKH_C.Text = "";
                            cb_MaSP_N.Text = "";
                            cb_MaCT_C.Text = "";
                            cb_SoKH_N.Text = "";
                        }
                        else
                            MessageBox.Show("Kiểm tra lại Mã CT hoặc Mã KH nhập");
                    }
                    else
                        MessageBox.Show("Chi Tiết này đã có trong DS phía dưới");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void buttonX1_Click(object sender, EventArgs e)
        {
            add_grid_Chuyen();
        }
        private void save_Chuyen()
        {
            
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Tran_Ins_Up";
            cmd.CommandType = CommandType.StoredProcedure;
            Conn.Open();
            cmd.Connection = Conn;

            try
            {
                UserXuat = "";
                if (dataGridView7.Rows.Count > 1)
                {

                    for (i = dataGridView7.Rows.Count - 2; i >= 0; i--)
                    {
                        //MaCT1 = "'Null'";

                        MaCT1 = dataGridView7.Rows[i].Cells["MaCT_C"].Value.ToString();
                        SoKH1 = dataGridView7.Rows[i].Cells["KH_C"].Value.ToString();
                        
                        SLNhap_d = double.Parse(dataGridView7.Rows[i].Cells["SL_C"].Value.ToString());
                        
                        flag1 = dataGridView7.Rows[i].Cells["flag_C"].Value.ToString();
                        Ngay1 = dataGridView7.Rows[i].Cells["NgayInsert_C"].Value.ToString();
                        UserNhap = lb_user.Text;
                        //insert_NhapKTG = @"insert into dbo.KV1_KTG(MaCT,SoBB,SoKH,SLNhap,Ngay,UserNhap,GhiChu)values(N'" + MaCT1 + "',N'" + SoBB1 + "',N'" + SoKH1 + "'," + SLNhap_d + ",'" + Ngay1 + "',N'" + UserNhap + "',N'"+GhiChu1+"')";
                        cmd.Parameters.AddWithValue("@MaCT", MaCT1);
                        cmd.Parameters.AddWithValue("@SoBB", "");
                        cmd.Parameters.AddWithValue("@SoKH", SoKH1);
                        cmd.Parameters.AddWithValue("@SLNhap", SLNhap_d);
                        cmd.Parameters.AddWithValue("@SLXuat", "0");
                        cmd.Parameters.AddWithValue("@Ngay", Ngay1);
                        cmd.Parameters.AddWithValue("@UserNhap", UserNhap);
                        cmd.Parameters.AddWithValue("@FSC", "");
                        cmd.Parameters.AddWithValue("@UserXuat", UserXuat);
                        cmd.Parameters.AddWithValue("@GhiChu", "Chuyển");
                        cmd.Parameters.AddWithValue("@flag", flag1);
                        cmd.Parameters.AddWithValue("@SoXe", "");
                        cmd.Parameters.AddWithValue("@ID", SoKH1);
                        cmd.Parameters.AddWithValue("@SLTheoDoi", SLNhap_d);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        dataGridView7.Rows.RemoveAt(i);

                    }
                    MessageBox.Show("Dữ Liệu được lưu thành công");
                    lb_Tong.Text = "0";
                    dta.reset_txt(groupPanel1);
                    cb_MaCT_C.Text = "";
                    cb_MaSP_C.Text = "";
                    cb_MaSP_N.Text = "";
                    cb_MaCT_N.Text = "";
                    cb_SoKH_C.Text = "";
                    cb_SoKH_N.Text = "";
                    cb_MaSP_C.Focus();
                }



            }
            catch (Exception ex)
            {
                string s = "insert duplicate key";
                string FK = "FOREIGN KEY constraint";
                string s1 = ex.Message.ToString();
                if (s1.Contains(s))
                { MessageBox.Show("Insert không thành công \n" + "Mã đã tồn tại "); }
                else if (s1.Contains(FK))
                { MessageBox.Show("Nhập sai thông tin trong Combobox"); }
                else
                    MessageBox.Show(ex.Message);
                MessageBox.Show("Đã xảy ra lỗi khi thêm dữ liệu, vui lòng kiểm tra lại");
            }
            finally
            {
                Conn.Close();
                cmd.Dispose();
            }
        }

        private void buttonX2_Click(object sender, EventArgs e)
        {
            save_Chuyen();
        }

        private void lb_user_TextChanged_1(object sender, EventArgs e)
        {
            Check_User();
        }

        private void tab_BangKe_Click(object sender, EventArgs e)
        {
            Data dta = new Data();
            SqlConnection Conn = dta.Connect();
            DataSet ds = new DataSet();
            if (dataGridView11.Rows.Count > 1)
                dataGridView11.Rows.Clear();
            try
            {
                string[] temp = txt_NgayBK.Text.Split('/');
                string_temp = temp[1] + "/" + temp[0] + "/" + temp[2];
                string_temp = @"SELECT [Serial],dbo.Load_CTSP_Mua.TenSP,dbo.Load_CTSP_Mua.MaChiTiet,dbo.Load_CTSP_Mua.TenChiTiet
     
                                  ,[SoBB]
                                  ,[SoXe]
                                  ,[SoKH]
                                  ,[SLNhap]
                                  ,[SLXuat]
                                  ,convert(varchar(10),[Ngay],103) as Ngay
                                  ,[NgayInsert]
                                  ,[GhiChu]
                                  ,[flag]
                              FROM [dbo].[KV1_KTG]
                              inner join dbo.Load_CTSP_Mua on dbo.Load_CTSP_Mua.MaChiTiet=dbo.KV1_KTG.MaCT where NgayInsert>='"+string_temp+" 00:00:00' and NgayInsert<='"+string_temp+" 23:59:59'";
                ds = dta.dts(string_temp);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        dataGridView11.Rows.Add(ds.Tables[0].Rows[i]["Serial"].ToString(), ds.Tables[0].Rows[i]["TenSP"].ToString(), ds.Tables[0].Rows[i]["MaChiTiet"].ToString(), ds.Tables[0].Rows[i]["TenChiTiet"].ToString(), ds.Tables[0].Rows[i]["SoBB"].ToString(), ds.Tables[0].Rows[i]["SoKH"].ToString(), ds.Tables[0].Rows[i]["SLNhap"].ToString(), ds.Tables[0].Rows[i]["SLXuat"].ToString(), ds.Tables[0].Rows[i]["Ngay"].ToString(), ds.Tables[0].Rows[i]["NgayInsert"].ToString(), ds.Tables[0].Rows[i]["SoXe"].ToString(), ds.Tables[0].Rows[i]["GhiChu"].ToString(), ds.Tables[0].Rows[i]["flag"].ToString());
                        dataGridView11.Rows[i].HeaderCell.Value = (i + 1).ToString();
                    }
                   
                    ds.Clear();
                    for (i = dataGridView11.Rows.Count - 2; i >= 0; i--)
                    {
                        if(dataGridView11.Rows[i].Cells["SLNhapBK"].Value.ToString()=="0")
                            dataGridView11.Rows[i].DefaultCellStyle.BackColor = Color.AliceBlue;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           
        }
        private void Exportexcel_TonKho()
        {
            try
            {
                Process prs = new Process();
                /*string Search = null;
                    Search = @"SELECT [MaSP]
                              ,[TenSP]
                              ,[MaCT]
                              ,[TenChiTiet]
                              ,[ChieuDaySC]
                              ,[ChieuRongSC]
                              ,[ChieuDaiSC]
                              ,[SoLuongCT]
                              ,[SLNhap]
                              ,[SLXuat],[TonKho]
                          FROM [dbo].[KTG_TK]";*/
                DataSet ds = report_search();

                ExportExcel(ds.Tables[0], "C:\\Huynh\\KTG_TK.xlsx", 6, 1);
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void InKHLP()
        {
            Process prs = new Process();
            try
            {
                if (txt_KHLP_report.Text != "")
                {
                    Ngay_start1 = txt_KHLP_report.Text;
                    string_temp = @"select qry2.TenSP+' - '+qry2.MaSP as TenSP,qry2.MaChiTiet,qry2.TenChiTiet,qry2.SoLuongCT,qry1.ChieuDaySC,qry1.ChieuRongSC,qry1.ChieuDaiSC,qry3.SLKH/qry2.SoLuongCT as SLLP,qry3.SLKH as SLXuat,qry2.ChieuDaySC*qry2.ChieuRongSC*qry2.ChieuDaiSC*qry3.SLKH/1000000000 as SoKhoi,qry2.Ve as DVT,'NGÀY '+convert(char(10),KeHoachSP.Ngay,103)+N'      TUẦN: '+convert(char(5),datepart(wk,KeHoachSP.Ngay)) as Ngay,qry2.LoaiGo
                From
                (select dbo.Load_CTSP_Mua.* from dbo.Load_CTSP_Mua where ChieuDaiSC<1500) as qry1
                right join (select dbo.Load_CTSP_Mua.* from dbo.Load_CTSP_Mua) as qry2 on qry1.MaChiTiet=qry2.MaChiTiet
                inner join (select dbo.KH_Theodoi.SLKH,MaCT,left(KH_Theodoi.ID_KeHoach,5) as MaKH,ID_KeHoach from dbo.KH_Theodoi where ID_KeHoach like '%" + txt_KHLP_report.Text + "KV1X2014') as qry3 on qry2.MaChiTiet=qry3.MaCT inner join KeHoachSP on KeHoachSP.ID=qry3.ID_KeHoach";
                    DataSet ds = prs.dts(string_temp);
                    Fr_Report FrR = new Fr_Report();
                    FrR.report_KV1_KHLP(ds, Ngay_start1);
                    FrR.Show();
                    ds.Clear();
                }
                else
                    MessageBox.Show("Vui lòng nhập kế hoạch");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            InKHLP();
        }
        private void FSC_input()
        {
            try
            {
                Process prs = new Process();
                string[] temp = txt_NgayStart.Text.Split('/');
                Ngay_start1 = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
                string[] temp2 = txt_Ngayend.Text.Split('/');
                Ngay_End1 = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
                string print_NKTG = @"select dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,dbo.ChiTietSP.LoaiGo,qry1.Serial as Serial_NK,qry1.SLNhap as SoLuong,(dbo.ChiTietSP.ChieuDaySC*dbo.ChiTietSP.ChieuRongSC*dbo.ChiTietSP.ChieuDaiSC)*qry1.SLNhap/1000000000 as SoKhoi,left(qry1.SoKH,5) as MaKeHoach,'' as DVT,qry1.FSC as NguonGo,qry1.Ngay
                                        from (select MaCT,Serial,SLNhap,SoKH,convert(varchar(10),Ngay,103) as Ngay,FSC from dbo.KV1_KTG";
                if (cb_MaSPReport.Text == "")
                {
                    print_NKTG += @" where SLNhap <>0 and Ngay>='" + Ngay_start1 + "' and Ngay<='" + Ngay_End1 + "' and FSC like '%FSC%') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet order by qry1.Serial";

                }
                else
                {
                    MaSP1 = prs.substring_right(cb_MaSPReport.Text, child);
                    print_NKTG += @" where SLNhap <>0 and Ngay>='" + Ngay_start1 + "' and Ngay<='" + Ngay_End1 + "' and FSC like '%FSC%') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet and ChiTietSP.MaSP='" + MaSP1 + "' order by qry1.Serial";
                }
                Ngay1 = "Gỗ FSC từ ngày: "+txt_NgayStart.Text + " - " + txt_Ngayend.Text;
                DataSet ds = prs.dts(print_NKTG);
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayinput.Text;
                FrR.report_KV1NKTG(ds, Ngay1);
                FrR.Show();
                ds.Clear();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void FSC_output()
        {
            try
            {
                Process prs = new Process();
                string[] temp = txt_NgayStart.Text.Split('/');
                Ngay_start1 = temp[1] + "/" + temp[0] + "/" + temp[2] + " 00:00:00";
                string[] temp2 = txt_Ngayend.Text.Split('/');
                Ngay_End1 = temp2[1] + "/" + temp2[0] + "/" + temp2[2] + " 23:59:59";
                string print_XKTG = @"select dbo.ChiTietSP.TenSP,dbo.ChiTietSP.TenChiTiet,dbo.ChiTietSP.LoaiGo,qry1.Serial as Serial_NK,qry1.SLXuat as SoLuong,(dbo.ChiTietSP.ChieuDaySC*dbo.ChiTietSP.ChieuRongSC*dbo.ChiTietSP.ChieuDaiSC)*qry1.SLXuat/1000000000 as SoKhoi,qry1.Ngay,left(qry1.SoKH,5) as MaKH,FSC as NguonGo
                                        from (select MaCT,Serial,SLXuat,SoKH,convert(varchar(10),Ngay,103) as Ngay,FSC from dbo.KV1_KTG";
                if (cb_MaSPReport.Text == "")
                {
                    print_XKTG += @" where SLXuat <>0 and Ngay>='" + Ngay_start1 + "' and Ngay<='"+Ngay_End1+"' and FSC like '%FSC%') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet order by qry1.Serial";
                }
                else
                {
                   
                        MaSP1 = prs.substring_right(cb_MaSPReport.Text, child);
                        print_XKTG += @" where SLXuat <>0 and Ngay>='" + Ngay_start1 + "' and Ngay<='"+Ngay_End1+"' and FSC like '%FSC%') as qry1 inner join dbo.ChiTietSP on qry1.MaCT=dbo.ChiTietSP.MaChiTiet where dbo.ChiTietSP.MaSP='" + MaSP1 + "' order by qry1.Serial";
                    
                }
                Ngay1 = "Gỗ FSC từ ngày: " + txt_NgayStart.Text + " - " + txt_Ngayend.Text;
                DataSet ds = prs.dts(print_XKTG);
                Fr_Report FrR = new Fr_Report();
                //Ngay1 = txt_Ngayoutput.Text;
                FrR.report_KV1XKTG(ds, Ngay1);
                FrR.Show();
                ds.Clear();
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            FSC_input();
        }

        private void linkLabel4_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            FSC_output();
        }
    }
}
